SQL Server merge two temp tables and add common columns as new row and add unmatch column using sql

k7fdbhmy  于 2023-03-11  发布在  其他
关注(0)|答案(2)|浏览(146)

I am using ms sql server, i have two tables below( table 1 and table 2):

table 1                   table 2                   result
name value      ++       name  data ==            name  value  data
test  10                 test1  20                test   10     null
                                                  test1   null   20

I want to merge table 1 and table 2 and my expected result would be as result table , can anybody help me here ?

uujelgoq

uujelgoq1#

-- start the merge statement 
MERGE
--identify the Target/destination table
#TempTable as X
--identify the Source/information table
USING #tempTableTwo as Y
--show it how to match up the two tables together.
on X.A = Y.A
-- put in logic to input a row if not in the source target already
WHEN NOT MATCHED BY X 
    THEN
        -- target table columns
        insert (A,b,c,d)
        -- values to put into the table
        values(x.a,Y.b,x.c,x.d)
dkqlctbz

dkqlctbz2#

You can combine these using a full join:

select coalesce(t1.name, t2.name) as name, t1.value, t2.data
from t1 full join
     t2
     on t1.name = t2.name;

If you want to use * to select all columns in the tables, SQL Server does not offer simple way to choose unique columns (without listing all columns). SQL Server doesn't support USING .

相关问题