How to merge two tables in SQL

Posted on 19. Jan, 2010 by Gia in Blog

In this article, I explain how to merge two tables using plain SQL.

When merging two tables, what you want to achieve is:

  • Create missing records (e.g. if Table A contains a record that cannot be found in Table B, then create such record in Table B);
  • Update mismatching records (e.g. if a record having the same unique key can be found in both tables but it has at least one mismatching non-key attribute, then update its values by copying them from the other table).

Following is the definition of the test tables we’re going to use in this example.

-- CREATE the 'UsersLeft' table.
CREATE TABLE [UsersLeft](
 [ID] [int] NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [Surname] [nvarchar](50) NULL
 CONSTRAINT [PK_UsersLeft] PRIMARY KEY CLUSTERED
 (
 [ID] ASC
 ))

-- Create the 'UsersRight' table;
CREATE TABLE [UsersRight](
 [ID] [int] NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [Surname] [nvarchar](50) NULL
 CONSTRAINT [PK_UsersRight] PRIMARY KEY CLUSTERED
 (
 [ID] ASC
 ))

Following is the SQL for creating missing records (without updating mismatching records):

BEGIN TRAN t1;

-- Insert missing records in the 'UsersLeft' table.
INSERT INTO
   UsersLeft
SELECT
   UsersRight.ID, UsersRight.[Name], UsersRight.Surname
FROM
   UsersRight
LEFT JOIN
   UsersLeft
ON
   UsersRight.ID = UsersLeft.ID
WHERE
   UsersLeft.ID IS NULL;

-- Insert missing records in the 'UsersRight' table.
INSERT INTO
   UsersRight
SELECT
   UsersLeft.ID, UsersLeft.[Name], UsersLeft.Surname
FROM
   UsersLeft
LEFT JOIN
   UsersRight ON UsersLeft.ID = UsersRight.ID
WHERE
   UsersRight.ID IS NULL;

COMMIT TRAN t1;

And following is the SQL for creating missing records and updating mismatching records with the values from the ‘UsersRight’ table:

BEGIN TRAN t1;

-- Update the 'Name' attribute in the UsersLeft table if
-- the value differs from the one if the UsersRight table.
UPDATE
 UsersLeft
SET
 UsersLeft.Name = UsersRight.Name
FROM
 UsersLeft
INNER JOIN
 UsersRight
ON
 UsersLeft.ID = UsersRight.ID
WHERE
 UsersLeft.Name <> UsersRight.Name;

-- Update the 'Surname' attribute in the UsersLeft table if
-- the value differs from the one if the UsersRight table.
UPDATE
 UsersLeft
SET
 UsersLeft.Surname = UsersRight.Surname
FROM
 UsersLeft
INNER JOIN
 UsersRight
ON
 UsersLeft.ID = UsersRight.ID
WHERE
 UsersLeft.Surname <> UsersRight.Surname;

COMMIT TRAN t1;

This and much more can also be done using Table Diff, our table comparison tool. Table Diff also allows you to merge tables located on disparate SQL Servers.

Hope this helps.

Leave a Reply