How can I copy the Column Data Of one Table To Another Table where the row data of one table matches column name of another

Question

I have two tables :

Table 1:
Id | PersonId |Variable  | Value|
1      12      FirstName  NULL
2      12      Address    NULL
------------------------
Table2:
Id | PersonId | FirstName| LastName| Address | Phone
1      12          Tommy      Stark     NY        12365

I need to copy data from table 2 into table 1 and I need output like:

Table 1:
Id | PersonId |Variable  | Value|
1      12      FirstName  Tommy
2      12      Address    NY

Sounds like you want to do something like this [answer](http://stackoverflow.com/questions/4428761/mysql-field-name-from-variable) — Buddy Yaussy, Feb 15 '16 at 07:36

score 0 · Answer 1 · answered Feb 15 '16 at 07:25

You could use a series of case expressions to match table 1 values to table 2 column names. It's clunky as heck, but it should work:

UPDATE t1
SET    t1.value = CASE t1.variable 
                  WHEN 'FirstName' THEN t2.firstname 
                  ELSE t1.value
                  END,
       t1.value = CASE t1.variable
                  WHEN 'LastName' THEN t2.lastname
                  ELSE t1.value
                  END,
       t1.value = CASE t1.variable
                  WHEN 'Address' THEN t2.address
                  ELSE t1.value
                  END,
       t1.value = CASE t1.phone
                  WHEN 'Phone' THEN t2.phone
                  ELSE t1.value
                  END
FROM   t1
JOIN   t2 ON t1.personid = t2.personid

thanks it worked. but is there anyother way? some sort of query where i do not need to write static hardcoded values . — Anuj Tamrakar, Feb 15 '16 at 08:28

score 0 · Answer 2 · answered Feb 15 '16 at 08:59

Declare @Table1 TABLE 
    (Id int, PersonId int, Variable varchar(9), Value varchar(4))
;

INSERT INTO @Table1
    (Id, PersonId, Variable, Value)
VALUES
    (1, 12, 'FirstName', NULL),
    (2, 12, 'Address', NULL)
;

DECLARE @Table2 TABLE 
    (Id int, PersonId int, FirstName varchar(5), LastName varchar(5), Address varchar(2), Phone int)
;

INSERT INTO @Table2
    (Id, PersonId, FirstName, LastName, Address, Phone)
VALUES
    (1, 12, 'Tommy', 'Stark', 'NY', 12365)


select TT.Id,
TT.PersonId,
TT.Variable,
CASE 
    WHEN T.col = TT.Variable 
    THEN T.val 
    END value 
from @Table1 TT
INNER JOIN (
select col,val from @Table2 t CROSS APPLY (values ('Id',CAST(Id AS VARCHAR)), ('PersonId',CAST(PersonId AS VARCHAR)),
('FirstName',CAST(FirstName AS VARCHAR)),
('LastName',CAST(LastName AS VARCHAR)),
('Address',CAST(Address AS VARCHAR)),
('Phone',CAST(Phone AS VARCHAR)))cs(col,val))T
ON T.col = TT.Variable

How can I copy the Column Data Of one Table To Another Table where the row data of one table matches column name of another

2 Answers2