Split column values using delimiter and insert them into referenced table

Question

Split column values using delimiter and insert them into referenced table

inHouseDBA

Default port

Points: 1403
More actions
January 31, 2019 at 10:46 pm

#409478

Hello,
I am copying few million rows from Table1 into Table2 via INSERT INTO...SELECT.
Caveat is that 1 column(Column5) in Table1 contains comma separated values, so upon insert into Table2 I need to split comma separated values in Column5 and insert them as individual values in referenced Table3. There are a number of examples around like:
https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns or
https://stackoverflow.com/questions/39401034/split-column-value-using-delimiter-and-insert-into-different-table?rq=1
but all of them deals with a few records. So is there a a good and painless way to tackle that issue when deal with few millions records.
Thank you

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24381 More actions · Answer 1

inHouseDBA - Thursday, January 31, 2019 10:46 PM
Hello,
I am copying few million rows from Table1 into Table2 via INSERT INTO...SELECT.
Caveat is that 1 column(Column5) in Table1 contains comma separated values, so upon insert into Table2 I need to split comma separated values in Column5 and insert them as individual values in referenced Table3. There are a number of examples around like:
https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns or
https://stackoverflow.com/questions/39401034/split-column-value-using-delimiter-and-insert-into-different-table?rq=1
but all of them deals with a few records. So is there a a good and painless way to tackle that issue when deal with few millions records.
Thank you

Take a look at Eirikur's string splitter which is based on Jeff's string splitter
Either of these should be a massive improvement on the code you listed.

How to post data/code on a forum to get the best help.

DesNorton SSC-Insane Points: 24381 More actions · Answer 2

DesNorton - Friday, February 1, 2019 12:12 AM
inHouseDBA - Thursday, January 31, 2019 10:46 PM
Hello,
I am copying few million rows from Table1 into Table2 via INSERT INTO...SELECT.
Caveat is that 1 column(Column5) in Table1 contains comma separated values, so upon insert into Table2 I need to split comma separated values in Column5 and insert them as individual values in referenced Table3. There are a number of examples around like:
https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns or
https://stackoverflow.com/questions/39401034/split-column-value-using-delimiter-and-insert-into-different-table?rq=1
but all of them deals with a few records. So is there a a good and painless way to tackle that issue when deal with few millions records.
Thank you
Take a look at Eirikur's string splitter which is based on Jeff's string splitter
Either of these should be a massive improvement on the code you listed.

Here is a sample of usage of Eirikur's splitter
CREATE TABLE #TableA ( ID int , DelimString varchar(100) );

CREATE TABLE #TableB ( ID int , Part1 int , Part2 datetime , Part3 varchar(20) );

INSERT INTO #TableA ( ID, DelimString ) VALUES ( 1, '5,17 jan 2019 10:45:12,hello' ) , ( 2, '11,2018/05/22 13:18:22,world' ) , ( 3, '999,9 jul 2017' )

INSERT INTO #TableB ( ID, Part1, Part2, Part3 ) SELECT a.ID , Part1 = CONVERT(int, MAX(CASE WHEN lst.ItemNumber = 1 THEN lst.Item END) ) , Part2 = CONVERT(datetime, MAX(CASE WHEN lst.ItemNumber = 2 THEN lst.Item END) ) , Part3 = CONVERT(varchar(20), MAX(CASE WHEN lst.ItemNumber = 3 THEN lst.Item END) ) FROM #TableA AS a CROSS APPLY dbo.DelimitedSplit8K_LEAD(a.DelimString, ',') AS lst GROUP BY a.ID;

SELECT * FROM #TableA; SELECT * FROM #TableB;

DROP TABLE #TableA, #TableB;

How to post data/code on a forum to get the best help.

inHouseDBA Default port Points: 1403 More actions · Answer 3

Thank you DesNorton, sorry I wasn't clear, but it is not exactly what I need.
Based on the TableB you populated, the final TableB layout should looks like below:
CREATE TABLE #TableB (
ID int
, Value varchar(20)
);

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

inHouseDBA - Friday, February 1, 2019 6:52 PM
Thank you DesNorton, sorry I wasn't clear, but it is not exactly what I need.
Based on the TableB you populated, the final TableB layout should looks like below:
CREATE TABLE #TableB (
ID int
, Value varchar(20)
);

I would say then that adapting the code supplied will give you the desired answer

DesNorton SSC-Insane Points: 24381 More actions · Answer 5

inHouseDBA - Friday, February 1, 2019 6:52 PM
Thank you DesNorton, sorry I wasn't clear, but it is not exactly what I need.
Based on the TableB you populated, the final TableB layout should looks like below:
CREATE TABLE #TableB (
ID int
, Value varchar(20)
);

Then just use the output of the splitter function diectly
CREATE TABLE #TableA ( ID int , DelimString varchar(100) );

CREATE TABLE #TableB ( ID int , Value varchar(20) );

INSERT INTO #TableA ( ID, DelimString ) VALUES ( 1, '5,17 jan 2019 10:45:12,hello' ) , ( 2, '11,2018/05/22 13:18:22,world' ) , ( 3, '999,9 jul 2017' )

INSERT INTO #TableB ( ID, Value ) SELECT a.ID , Value = lst.Item FROM #TableA AS a CROSS APPLY dbo.DelimitedSplit8K_LEAD(a.DelimString, ',') AS lst;

SELECT * FROM #TableA; SELECT * FROM #TableB;

DROP TABLE #TableA, #TableB;

How to post data/code on a forum to get the best help.

inHouseDBA Default port Points: 1403 More actions · Answer 6

inHouseDBA

Default port

Points: 1403

February 2, 2019 at 7:58 pm

#2020939

Thank you DesNorton

HappyGeek SSCoach Points: 18729 More actions · Answer 7

Since this is SQL 2016 you could achieve the same using the string_split function. I would add the caveat that I have not tested the performance of this against either Eirikur's string splitter or Jeff's string splitter, might be an interesting to do......

...

ScottPletcher SSC Guru Points: 101090 More actions · Answer 8

HappyGeek - Sunday, February 3, 2019 2:04 AM
Since this is SQL 2016 you could achieve the same using the string_split function. I would add the caveat that I have not tested the performance of this against either Eirikur's string splitter or Jeff's string splitter, might be an interesting to do......

I don't believe that is a safe way to do this, since STRING_SPLIT does not guarantee the order of results.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".