Hi,
I have 2 tables variables as seen below :
@a
[Deptname]
[Location]
[Column1]
[Column2]
[Column3]
@b
testval1,
testval2,
30,
50,
60
The first one i.e.
@a
has all the column names & the second i.e.
@b
has all the field values.
Need to loop through these 2 table variables & form an insert statement to insert the columns & corresponding values in a third table.
i.e.
insert into my_table ([Deptname],[Location],[Column1],[Column2],[Column3])
values ('testval1','testval2','30','50','60')
Any help would be much appreciated.
What I have tried:
declare @a varchar(100)
set @a = '[Deptname],[Location],[Column1],[Column2],[Column3]'
select
a.value('.', 'varchar(max)')
from
(select cast('<m>' + REPLACE(@a, ',', '<m>') + '' AS XML) as col) as A
CROSS APPLY A.col.nodes ('/M') AS Split(a)
declare @b varchar(100)
set @b = 'testval1','testval2','30','50','60'
select
b.value('.', 'varchar(max)')
from
(select cast('<m>' + REPLACE(@b, ',', '<m>') + '' AS XML) as col) as B
CROSS APPLY B.col.nodes ('/M') AS Split(B)