SQL Server – Using MERGE to insert, update or delete data

Using the MERGE statement, you can synchronize two tables. In other words, you can insert, update or delete data in a target table based on a source table using a single statement.

EXAMPLE

DECLARE @TennisRackets_Source table(
id int NOT NULL PRIMARY KEY,
name varchar(40) NOT NULL,
price money NOT NULL);
 
DECLARE @TennisRackets_Target table(
id int NOT NULL PRIMARY KEY,
name varchar(40) NOT NULL,
price money NOT NULL);
 
INSERT INTO @TennisRackets_Source VALUES
(1, ‘Head Prestige Pro’, 150),
(2, ‘Head Prestige MP’, 130),
(3, ‘Head Prestige S’, 120),
(4, ‘Head Speed Rev Pro’, 145),
(5, ‘Head Speed MP A’, 135),
(6, ‘Head Speed S’, 125);
 
INSERT INTO @TennisRackets_Target VALUES
(1, ‘Head Prestige Pro’, 150),
(2, ‘Head Prestige MP’, 140),
(3, ‘Head Prestige S’, 120),
(4, ‘Head Speed Rev Pro’, 135),
(7, ‘Head Radical MP’, 110);
COMPARING DATA IN SOURCE AND TARGET TABLES
SELECT * FROM  @TennisRackets_Source;
SELECT * FROM  @TennisRackets_Target;

MERGE_01

  • The rows with ids = 1, 2, 3, 4 are matched (source id = target id). The target rows will be updated with the respective source rows.
  • The rows with ids = 5, 6 are not matched by a target id. These rows will be inserted in the target table.
  • The row with id = 7 is not matched by a source id. This rows will be deleted from the target table.

USING THE MERGE STATEMENT TO SYNCHRONIZE THE TABLES

MERGE INTO @TennisRackets_Target AS TGT
USING @TennisRackets_Source AS SRC
ON SRC.id = TGT.id
WHEN matched THEN UPDATE
   SET TGT.NAME = SRC.NAME,
           TGT.price = SRC.price
WHEN NOT matched THEN INSERT
   VALUES(SRC.id, SRC.NAME, SRC.price)
WHEN NOT matched BY source THEN DELETE;

RESULT SETS AFTER THE EXECUTION

SELECT * FROM  @TennisRackets_Source;
SELECT * FROM  @TennisRackets_Target;
MERGE_02

Observe that the rows are identical after the MERGE statement execution.

Using predicates to avoid unnecessary updates

We can also use predicates to avoid updates when there is no difference between target and source rows.

Look at the following example:

MERGE INTO @TennisRackets_Target AS TGT
USING @TennisRackets_Source AS SRC
ON SRC.id = TGT.id
WHEN matched  AND (SRC.name <> TGT.name OR SRC.price <> TGT.price) THEN UPDATE
   SET TGT.NAME = SRC.NAME,
           TGT.price = SRC.price
WHEN NOT matched THEN INSERT
   VALUES(SRC.id, SRC.NAME, SRC.price)
WHEN NOT matched BY source THEN DELETE;

Now the target table will be updated only when the source id is equal to the target id and one of the nonkey columns (name or price) have different values.

Advertisements

One thought on “SQL Server – Using MERGE to insert, update or delete data

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s