Insert comma delimited ids into a table

Question

I need to insert records into a SQL Server table. The client send ids I need to insert into a specific column in the table:

(2,4,123,1357,1234,5657,753);

Using this function I'm splitting the comma delimited string, but not sure how to insert it to the table along with the other columns

I need to create something that will generate inserts such as:

insert into table_name (id,column_2,column_3) values (2, column_s_some_value, column_3_some_value);
insert into table_name (id,column_2,column_3) values (4, column_s_some_other_value, column_3_some_value);
insert into table_name (id,column_2,column_3) values (123, column_s_some_value, column_3_some_value);
ETC...

How can I achieve that?

You could cross join to [table valued function string_spilt](https://stackoverflow.com/questions/37616831/string-split-is-not-a-recognized-built-in-function-name) and write dynamic SQL to do the insert with values (),(),(),() — xQbert, Feb 27 '18 at 19:39

score 2 · Accepted Answer · answered Feb 27 '18 at 19:41

the split function is a Table-Valued Function, which means it can be treated as a table, and you can do an INSERT..SELECT

insert into table_name (id,column_2,column_3)
SELECT s.item, column_s_some_value, column_3_some_value
FROM Split(@input_string, ',') s
{JOINS if needed to get other column values}

score 1 · Answer 2 · answered Feb 28 '18 at 09:33

1

If you do want to split strings, the most efficient way to get that done is through the use of the tally table as outlined here by Jeff Moden. I'd strongly suggest you use this method in just about any version of SQL Server.

answered Feb 28 '18 at 09:33

Grant Fritchey

2,645
19
21

Insert comma delimited ids into a table

2 Answers2