sql - How to store semicolon separated long string into multiple columns? -

basically due restriction on live server, cannot use text file need hard code data in t-sql code.

so first created string text file this:

("001122;sale item 1", "001123;sale item 23", "001124;sale item 24", .... )   

i have table structure:

declare @product table(productcode int not null, description nvarchar(100) not null) 

first need store code , description in table variable. once that's done, can map physical table , update records.

how can achieve similar to:

insert @product(productcode, description)  values ("001122;sale item 1", "001123;sale item 23", "001124;sale item 24", .... ) 

 code        description   001122      sale item1  001123      sale item2  001124      sale item3 

i crated sample you, please check this

declare @questions varchar(100)= '"001122;sale item 1", "001123;sale item 23", "001124;sale item 24"'  declare @myxml xml  = n'<h><r>' +replace(@questions, ',', '</r><r>') + '</r></h>'  select @myxml  ;with cte  (     select cast(n'<h><r>' +  replace(vals.id.value('.', 'nvarchar(50)') ,';' , '</r><r>') + '</r></h>' xml) val      @myxml.nodes('/h/r') vals(id)  )  ,mycte1 (  select   distinct     replace( s.a.value('(/h/r)[1]', 'nvarchar(50)') , '"', '') c1,        replace( s.a.value('(/h/r)[2]', 'nvarchar(50)') , '"', '') c2  cte cross apply val.nodes('/h/r') s(a) )  select * mycte1  

the output be

c1      c2 001123  sale item 23 001124  sale item 24 001122  sale item 1