How to merge two tables in SQL SERVER?

Question

I have the following two tables T1 and T2.

Table T1

Id   Value1

1    2
2    1
3    2

Table T2

Id   Value2

1    3
2    1
4    1

I need a SQL SERVER query to return the following

Id   Value1   Value2
1    2        3
2    1        1
3    2        0
4    0        1

Thanks in advance!!

Possible duplicate of [SQL JOIN and different types of JOINs](http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — Tab Alleman, May 18 '16 at 15:50

score 2 · Accepted Answer · answered May 18 '16 at 10:42

You can achieve this by FULL OUTER JOIN with ISNULL

Execution with given sample data:

DECLARE @Table1 TABLE (Id INT, Value1 INT)
INSERT INTO @Table1 VALUES (1, 2), (2, 1), (3, 2)

DECLARE @Table2 TABLE (Id INT, Value2 INT)
INSERT INTO @Table2 VALUES (1, 3), (2, 1), (4, 1)

SELECT ISNULL(T1.Id, T2.Id) AS Id, 
       ISNULL(T1.Value1, 0) AS Value1, 
       ISNULL(T2.Value2, 0) AS Value2
FROM @Table1 T1
FULL OUTER JOIN @Table2 T2 ON T2.Id = T1.Id

Result:

Id   Value1   Value2
1    2        3
2    1        1
3    2        0
4    0        1

Bridge · Answer 2 · 2016-05-24T11:43:08.333

FYI - Merge means something different in SQL Server.

I would suggest if you have a table which contains a list of all possible Id values, I would select everything from that and have two left outer joins to T1 and T2.

Assuming there isn't one, with only what is provided, it sounds like you want a full outer join.

Something like this should work:

SELECT Id = COALESCE(T1.Id, T2.Id),
       Value1 = COALESCE(T1.Value1, 0),
       Value2 = COALESCE(T2.Value2, 0)
FROM   T1
       FULL OUTER JOIN T2
                    ON T1.ID = T2.ID

score 0 · Answer 3 · answered May 18 '16 at 10:38

That's a basic FULL OUTER JOIN:

SELECT coalesce(t1.id,t2.id) as [ID],
       coalesce(t1.value1,0) as Value1,
       coalesce(t2.value2,0) as Value2
FROM t1
FULL OUTER JOIN t2
 ON(t1.id = t2.id)

This will join both table together, and will keep the record from both tables even when there is no match.

The COALESCE() is used to replace null values, because of the full join.

score 0 · Answer 4 · answered May 18 '16 at 10:52

Please try this. Its working fine with SQL Server 2012.

--Create two temp tables
    CREATE TABLE #temp1 
    (Id int,Value1 int)

    CREATE TABLE #temp2 
    (Id int,Value2 int)
--Insert Values to two tables
    INSERT INTO #temp1
    (Id,Value1)
    VALUES
    (1,2),(2,1),(3,3)

    INSERT INTO #temp2
    (Id,Value2)
    VALUES
    (1,3),(2,1),(4,1)
--Query which gives desired output
    SELECT 
        ID = COALESCE(#temp1.Id, #temp2.Id),
        Value1 = COALESCE(#temp1.Value1, 0),
        Value2 = COALESCE(#temp2.Value2, 0)
    FROM   
        #temp1 FULL JOIN #temp2 ON #temp1.Id = #temp2.Id

score 0 · Answer 5 · edited May 18 '16 at 11:01

0

Here is the general syntax for full join.

SELECT column-names
  FROM Table1 FULL OUTER JOIN Table2 
    ON column-name1 = column-name2
 WHERE condition

edited May 18 '16 at 11:01

Arulkumar

12,966
14
47
68

answered May 18 '16 at 10:58

Priti

1
1

How to merge two tables in SQL SERVER?

5 Answers5