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: 3781
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 9 posts - 1 through 8 (of 8 total)

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

pietlinden SSC Guru Points: 63503 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: 604 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: 604 More actions · Answer 3

Duplicate

This reply was modified 4 months, 3 weeks ago by Dennis Jensen.

pietlinden SSC Guru Points: 63503 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: 246960 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 4 months, 3 weeks ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Budd Hall of Fame Points: 3781 More actions · Answer 6

Sorry for the delay. I'm sure I'm not the only one with multiple projects and DAILY priority changes.

Here is some sample data

I have already used DelimitedSplit8K to pull the data from the varbinary field and build it into a single string without the field names.

Here is a sample; Where XYZ009X09 is an added value from @v1

VALUES ('XYZ009X09','000099007PPP', '00001', '*N/A RowName*', '1.0000', 'Who is the Rookie', '=', 'Never you Mind', '000034005PPP', '00001', '*N/A RowName*', '2.0000', 'What is the Status', '=', 'Unknown', '000055009PPP', '00001', '*N/A RowName*', '3.0000', 'When does this conversion start', '=', 'Any time necessary')

And the results need to be something that can be used in an insert statement such as this

VALUES ('XYZ009X09','000099007PPP', '00001', '*N/A RowName*', '1.0000', 'Who is the Rookie', '=', 'Never you Mind'),

('XYZ009X09','000034005PPP', '00001', '*N/A RowName*', '2.0000', 'What is the Status', '=', 'Unknown'),

('XYZ009X09','000055009PPP', '00001', '*N/A RowName*', '3.0000', 'When does this conversion start', '=', 'Any time necessary')

Not sure if you want to see this but JIC;

This is how my Values String gets populated

SELECT @columns_c = 'VALUES ('+@v1+','+@columns_c+')'

After adding this I see how it might be helpful to understand that there are 2 issues, how to seperate @columns_c into X strings and then how to add 'VALUES ('+@v1+', to the beginning of the first string replace the , after the 47th value with ), - then add ( to the beginning of the remaining strings and then close the last string with )

I hope I have explained this well enough. Let me know what I've failed to explain properly.

Thanks So much.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

If the original input string is necessarily an NVARCHAR(anything), then DelimitedSplit8K is probably the wrong tool to use because it will slaughter certain NVARCHAR characters in the conversion to VARCHAR.

Where are these "strings" coming from? A file or ???? THIS IS REALLY IMPORTANT because if it's coming from a file, this could be super simple.

And we need to see original data, please. Not data that you've modified.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Budd Hall of Fame Points: 3781 More actions · Answer 8

The Original string is coming from a varbinary field. I might have solved this using a cursor and the group number from output of DelimitedSplit8K.