0

I have a string @str =11,22 in a variable. how can I insert this into a table like,

 id      num
-------------
 1       11
 2       22
xQbert
  • 34,733
  • 2
  • 41
  • 62
Giri Prasad
  • 1,175
  • 2
  • 8
  • 13
  • Duplicate of http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns – gh9 May 15 '15 at 14:20
  • `INSERT INTO tableName (NUM) VALUES (11),(22)` assuming ID is identity [FIDDLE](http://sqlfiddle.com/#!6/29b02/1/0) So.. what's the question? – xQbert May 15 '15 at 14:22
  • possible duplicate of [How do I split a string so I can access item x](http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) – Andrey Korneyev May 15 '15 at 14:22
  • You might find [this](http://sqlperformance.com/2012/07/t-sql-queries/split-strings) article helpful – Giorgos Betsos May 15 '15 at 14:22

1 Answers1

1

Here's a simple way to get what you're looking for although it is dependent on the values being split by a comma and having no spaces (although you can use trim if you believe this could happen):

Declare @str varchar(10) = '11,22,33'
Declare @foobar table (id int identity (1,1), num int)

while (CHARINDEX(',', @str) > 0)
begin
insert into @foobar (num)
Select Left(@Str, CHARINDEX(',', @str) - 1)
Set @str = (select SUBSTRING(@str, CHARINDEX(',', @str) + 1, Len(@str)))
end
if Len(@Str) > 0
    insert into @foobar (num) 
    select @str

select * from @foobar

And here is the SQL Fiddle: http://www.sqlfiddle.com/#!6/0e240/2/0

Christian Barron
  • 2,695
  • 1
  • 14
  • 22