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)|浏览(150)

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 ?

sql-server

来源：https://stackoverflow.com/questions/61081471/merge-two-temp-tables-and-add-common-columns-as-new-row-and-add-unmatch-column-u

2条答案

按热度按时间

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)

赞(0）回复(0）举报 2023-03-11

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 .

赞(0）回复(0）举报 2023-03-11

我来回答

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

2条答案

相关问题

热门标签

最新问答