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