I need to split a row in a select statement and paste into a table

Question

I have data in one table. I need to copy it to another table. One of the columns is a line with text delimiters. So I think, in order to select all the columns, insert the indentity value and with the subquery to split based on the separator and paste it into another table.

Here is sample data

ID Name City Items 1 Michael Miami item|item2|item3|item4|item5 2 Jorge Hallandale item|item2|item3|item4|item5 

copy Name , City into one table. and split and copy the Elements to another table with the value of the Identity column

So the conclusion should be

User table

 UserID Name City 1 Michael Miami 2 Jorge Hallandale 

...

 Items table ItemID UserID Name 1 1 Item 2 1 Item2 3 1 Item3 4 1 Item4 5 2 Item 6 2 Item2 7 2 Item3 8 2 Item4 

Not sure how to do this with T-SQL. Answers with examples will be appreciated.

Answer 1

You can create a custom function to split the string in T-Sql. You can then use the Split function as part of a JOIN with a base table to generate final results for your INSERT . Take a look at this post . I hope for this help.

Answer 2

You can do this using xml and cross apply.

See the following:

 DECLARE @t table (ID int, Name varchar(20), City varchar(20), Items varchar(max)); INSERT @t SELECT 1,'Michael','Miami' ,'item|item2|item3|item4|item5' UNION SELECT 2,'Jorge' ,'Hallandale','item|item2|item3|item4|item5' DECLARE @u table (UserID int identity(1,1), Name varchar(20), City varchar(20)); INSERT @u (Name, City) SELECT DISTINCT Name, City FROM @t DECLARE @i table (ItemID int identity(1,1), UserID int, Name varchar(20)); WITH cte_Items (Name, Items) as ( SELECT Name ,CAST(REPLACE('<r><i>' + Items + '</i></r>','|','</i><i>') as xml) as Items FROM @t ) INSERT @i (UserID, Name) SELECT u.UserID ,s.Name as Name FROM cte_Items t CROSS APPLY (SELECT i.value('.','varchar(20)') as Name FROM t.Items.nodes('//r/i') as x(i) ) s INNER JOIN @uu ON t.Name = u.Name SELECT * FROM @i 

More details here: http://www.kodyaz.com/articles/t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx

Answer 3

Can you do this with recursion? My T-SQL is rusty, but this may help you in the right direction:

 WITH CteList AS ( SELECT 0 AS ItemId , 0 AS DelimPos , 0 AS Item_Num , CAST('' AS VARCHAR(100)) AS Item , Items AS Remainder FROM Table1 UNION ALL SELECT Row_Number() OVER(ORDER BY UserID) AS ItemId , UserID , CASE WHEN CHARINDEX('|', Remainder) > 0 THEN CHARINDXEX('|', Remainder) ELSE LEN(Remainder) END AS dpos , Item_num + 1 as Item_Num , REPLACE(Remainder, '|', '') AS Element , right(Remainder, dpos+1) AS Remainder FROM CteList WHERE dpos > 0 AND ItemNum < 20 /* Force a MAX depth for recursion */ ) SELECT ItemId , Item FROM CteList WHERE item_num > 0 ORDER BY ItemID, Item_Num 

Answer 4

for this question, see How to split cell contents and extract information into a new column in SQL select statement? . it is very similar to what you need, but you will have to change it a bit, perhaps using the OUTPUT statement to capture the identifiers in this process.