I have three tables
Table1:
| Employee | Designation |
|---|
Table2:
| ColumnA | ColumnB |
|---|---|
| Ravi | Developer |
| Swathi | HR |
Table3:
| Sourcecolumn | Destinationcolumn |
|---|---|
| Employee | ColumnA |
| Designation | ColumnB |
Expected Output to be Inserted in Table1:
| Employee | Designation |
|---|---|
| Ravi | Developer |
| Swathi | HR |
I have to insert Table2 values in Table1 by matching the columns mentioned in Table3 dynamically.
Table2 columns and values will differ dynamically. Only by using Table3 I can insert in Table1. So I struck on this.
Can anyone help me with this?
New contributor
Gowsi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
4
You need to dynamically construct
INSERT INTO Table1 ([Employee],[Designation]) VALUES
('Ravi','Developer'),
('Swathi','HR');
from Tables 2 & 3.
Here is one way to do it using STUFF:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@vals AS NVARCHAR(MAX)
--generate [Employee],[Designation]
select @cols = STUFF((SELECT ',' + QUOTENAME(Sourcecolumn)
from Table3
order by Destinationcolumn
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
--generate ('Ravi','Developer'),('Swathi','HR')
select @vals = STUFF((SELECT ',' + '(''' + ColumnA + ''',''' + ColumnB + ''')'
from Table2
order by ColumnA
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
--Generate INSERT INTO Table1 ([Employee],[Designation]) VALUES ('Ravi','Developer'),('Swathi','HR')
set @query = 'INSERT INTO Table1 (' + @cols +') VALUES ' + @vals;
execute(@query)
SELECT * FROM Table1
fiddle
| Employee | Designation |
|---|---|
| Ravi | Developer |
| Swathi | HR |
1