I need to split string in select statement and insert to table

Question

I have a data in one table. I need to copy it to another table. One of the column is text delimited string. So what I'm thinking to select all columns insert get indentity value and with subquery to split based on delimiter and insert it to another table.

Here is the data example

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

copy Name, City to one table get identity and split and copy Items to another table with Identity Column Value

So output should be

Users 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 really sure how to do it with T-SQL. Answers with examples would be appreciated

Answer 1

You may create you custom function to split the string in T-Sql. You could then use the Split function as part of a JOIN with your base table to generate the final results for your INSERT statement. Have a look at this post. Hope 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 @u u ON t.Name = u.Name 


SELECT * FROM @i 

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

Answer 3

Can you accomplish this with recursion? My T-SQL is rusty but this may help send 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