How to join row values with Column names in SQL

Question

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?

You fix your design; this is not a good design. You're going to need dynamic SQL and it'll get ugly fast. Start by redesigning your tables so you don't have such a requirement. — Thom A
– Thom A ♦, Commented Aug 15, 2024 at 12:37
Side note: SQL Server 2012 ran out of support 2 years ago. You really need to implement your upgrade plan from 2020/2021 asap. — Thom A
– Thom A ♦, Commented Aug 15, 2024 at 12:38
Aren't source and destination mixed up? since the final result is Employee/Designation, not ColumnA, ColumnB? This sounds like a simple dynamic sql, so start reading on how to use it — siggemannen
– siggemannen, Commented Aug 15, 2024 at 12:40
This looks like a strange implementation of an EAV (entity attribute value) anti-pattern. This design will do nothing but cause you pain, anguish and persistent performance problems. — Sean Lange
– Sean Lange, Commented Aug 15, 2024 at 13:17

Bart McEndree · Accepted Answer · 2024-08-15 14:22:04Z

0

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

edited Aug 15, 2024 at 14:22

answered Aug 15, 2024 at 14:11

Bart McEndree

3,6391 gold badge12 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bart McEndree Over a year ago

If you had SQL Server 2017+ you could use STRING_AGG instead of STUFF+XML PATH

T N Over a year ago

RIght idea, but the @vals calculation above is open to a SQL injection attack - fiddle. I would suggest that you instead generate a source column list similar to the target column list (ensuring that they have the same sort order) and then combine them into a statement INSERT INTO Table1 ( <target columns> ) SELECT <source columns> FROM Table2. fiddle. (The logic is somewhat confusing in that the source and destination columns in the OP data appear to be backwards.)

Collectives™ on Stack Overflow

How to join row values with Column names in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related