Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

tsql

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

like image 605
Michael Born Avatar asked May 13 '11 17:05

Michael Born


3 Answers

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.

like image 88
FIre Panda Avatar answered Sep 21 '22 00:09

FIre Panda


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

like image 24
CStroliaDavis Avatar answered Sep 22 '22 00:09

CStroliaDavis


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
like image 45
Rob Paller Avatar answered Sep 22 '22 00:09

Rob Paller