0

I'm trying to create a stored procedure in SQL Server 2008 that takes a comma separated list of ints and adds them to a table.

For this example, the table has 1 column. So the stored procedure would just take a string like '1,2,3' and insert 3 rows into the table.

I'm not really sure how to go about doing this. Do I have to write something to split the string and then manually loop through the ids? Can I do something like

insert into table values split(idsString)

and somehow do it in one statement instead of a manual loop?

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
bdwain
  • 1,525
  • 13
  • 32
  • Try this answer over here : http://stackoverflow.com/questions/10914576/tsql-split-string –  May 23 '13 at 01:09
  • @MatthewMartin also not the most efficient way. So much so that [after my tests here](http://sqlblog.com/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx) I didn't even bother including them [in an updated set of tests](http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings). CLR was by far the fastest, if you can't use [table-valued parameters (my preference)](http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql). XML is a good compromise for INTs if either of those prove difficult to implement. – Aaron Bertrand May 23 '13 at 01:20
  • @AaronBertrand I like the suggestion you gave with the table-value parameters, but you never explained how to populate the table unless you use a DataTable. What if all I have access to is a comma separated list of ids in a string? – bdwain May 23 '13 at 03:32

0 Answers0