Insert (x) rows into table with y columns from string

Question

Insert (x) rows into table with y columns from string

Budd

Hall of Fame

Points: 3779
More actions
June 28, 2024 at 8:53 pm

#4432249

First I want to thank anyone who takes a moment to help with this. I really appreciate all the help I've gotten here over the years and I wish I were smart enough to contribute. Now on to my problem.
I need to parse out a string of data to be inserted into (@y) number of fields in a table. The string is NVARCHAR(MAX) and comma separated. I've gotten it to a point where I get a string such as VALUES(@v1,'','','','','','','','','','','','','','','',......) and need to break that into something more like VALUES(@v1,'','','','',''),(@v1'','','','',''),(@v1'','','','',''),(@v1......) (@x) times. As I write this I think it might now be so hard to just insert into the string at (@y) or (@y+1) ) ,(@v, might not need the trailing comma. But how?????
Thanks for any advise ~ Have a happy Independence Day

Viewing 6 posts - 1 through 5 (of 5 total)

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

pietlinden SSC Guru Points: 63457 More actions · Answer 1

This is absolutely a post which would benefit immensely from a sample record or two.

I see no reason why you can't use something like DelimitedSplit8K or SPLIT_STRING() to separate the delimited list into separate columns and then use INSERT.

maybe use a CTE to return the split list and then use something like

INSERT INTO DestinationTable (col1, col2...)

SELECT col1, col2,...

FROM cteDelimSplit

WHERE...

Dennis Jensen SSChasing Mays Points: 600 More actions · Answer 2

I am not sure what the issue you are having exactly but it seems if you just looked up String_Split that you would be able to implement what it is you are trying to implement as that is a fairly straight forward element of most SQL languages.

Dennis Jensen SSChasing Mays Points: 600 More actions · Answer 3

Duplicate

This reply was modified 2 days, 21 hours ago by Dennis Jensen.

pietlinden SSC Guru Points: 63457 More actions · Answer 4

DECLARE @TextToParse VARCHAR(100) = 'for col1, for col2, for col3, for col4';

INSERT INTO #SomeTable(SplitValue)
SELECT TRIM(value) AS SplitValue
FROM string_split(@TextToParse,',');

is one way

Phil Parkin SSC Guru Points: 246786 More actions · Answer 5

Budd wrote:

First I want to thank anyone who takes a moment to help with this. I really appreciate all the help I've gotten here over the years and I wish I were smart enough to contribute. Now on to my problem.
I need to parse out a string of data to be inserted into (@y) number of fields in a table. The string is NVARCHAR(MAX) and comma separated. I've gotten it to a point where I get a string such as VALUES(@v1,'','','','','','','','','','','','','','','',......) and need to break that into something more like VALUES(@v1,'','','','',''),(@v1'','','','',''),(@v1'','','','',''),(@v1......) (@x) times. As I write this I think it might now be so hard to just insert into the string at (@y) or (@y+1) ) ,(@v, might not need the trailing comma. But how?????
Thanks for any advise ~ Have a happy Independence Day

How about posting some sample data & desired results, in consumable format?

This reply was modified 11 hours, 1 minute ago by Phil Parkin.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.