Thursday 21 September 2017

Insert SQL Comma seprated data

create table #tbl0(Srno int,value int)
insert #tbl0
select Row_number() over (order by (select 1) ) as Srno,value from dbo.Split(@arrRelId, ',')

create table #tbl1(Srno int,value int)
insert #tbl1
select Row_number() over (order by (select 1) ) as Srno,value from dbo.Split(@arrRelCAmt, ',')

create table #tbl2(Srno int,value DATE)
insert #tbl2
select Row_number() over (order by (select 1) ) as Srno,value from dbo.Split(@arrDOB, ',')

INSERT INTO GCI_InsureInfo (GCI_ID,RelationId,SUMINSURE,DOB,Salutation)
SELECT @identity,T.value,S.value,CONVERT(DATE,Q.value,101),(CASE WHEN T.value in (2,11) THEN 'Mrs.' WHEN T.value in(1,3,4,12) THEN 'Mr.' WHEN T.value in(7,8) THEN 'Miss.' ELSE '' END)
FROM #tbl0 T
Inner Join #tbl1 S on S.Srno=T.Srno
inner Join #tbl2 Q ON Q.Srno = S.Srno AND Q.Srno = T.Srno