sql server - Split comma delimited string and insert to a table (int)

Question

i have a table with 3 columns (First_ID,Second_ID,Third_ID) all columns are int columns.

Now I have 3 values, first and third values are int values (1 and 0), the second value is a comma delimited string ('188,189,190,191,192,193,194')

what should be my approach to populate the table like the bellow:

1   188 0
1   189 0
1   190 0
1   191 0
1   192 0
1   193 0
1   194 0

I have tried different ways but could not get it to work as i want.

Thanks in advance

Answer 1

Better use XML for this,

Declare @Var nvarchar(MAX)

Set @Var ='188,189,190,191,192,193,194'

DECLARE @XML AS XML

DECLARE @Delimiter AS CHAR(1) =','

SET @XML = CAST(('<X>'+REPLACE(@Var,@Delimiter ,'</X><X>')+'</X>') AS XML)

DECLARE @temp TABLE (ID INT)

INSERT INTO @temp

SELECT N.value('.', 'INT') AS ID FROM @XML.nodes('X') AS T(N)

SELECT * FROM @temp