SQL Merge

It is a very common task to check existing database records prior to executing INSERT, UPDATE, or DELETE. Typically, we will check the database records using a SELECT statement, then we implement IF/ELSE statements to execute an INSERT/UPDATE/DELETE statement based on what the results of the SELECT were.

There is a better way – MERGE INTO.

The MERGE statement gives us a formal method of creating a temporary table, adding data to that temporary table, then comparing the temp table with the data that is in the table that we would like to INSERT/UPDATE/DELETE.

Here’s how it works:

--Param declarations
@Field1 ...
@Field2...
@Field3...
@Field4...

/*Check for existing record based on three fields (identity constraint)*/

--Specify the table that you want to INSERT/UPDATE/DELETE records to/from ("tt" is just an alias)
MERGE INTO dbo.TargetTable AS tt 
--Get ready to make our temporary table (source table)
USING 
--Here is where we add data to the temporary table.
(SELECT @Field1 AS Field1, @Field2 AS Field2, @Field3 AS Field3) AS tmp 
-- Now we are going to join the target table with our temporary table
ON (tt.Field1 = tmp.Field1 AND tt.Field2 = tmp.Field2  AND tt.Field3 = tmp.Field3)
-- If a match is found in the target table and the temporary table, then execute an UPDATE
WHEN MATCHED THEN 
UPDATE
SET Field4 = @Field4
--If no matches were found, then INSERT a new record
WHEN NOT MATCHED THEN 
INSERT (Field1, Field2, Field3)
VALUES (@Field1, @Field2, @Field3);
RETURN @@IDENTITY
Advertisements

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