With the Merge Statement, we can run INSERT, UPDATE, and DELETE commands at one time to synchronize two tables of the same structure.
First, let’s create source and target tables with the following script, and add a few records to these tables.
As you can see in the Script, some of the records in the two tables are different. We will synchronize the records in these two tables using the MERGE statement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE [TestDB] GO CREATE TABLE [dbo].[MyTableTarget]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[MyTableSource]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL ) ON [PRIMARY] GO INSERT INTO [dbo].[MyTableTarget] VALUES ('Nurullah CAKIR'),('Faruk ERDEM') INSERT INTO [dbo].[MyTableSource] VALUES ('Nurullah CAKIR'),('Faruk ER'),('Hakan GURBASLAR') |
We then sync the source and target tables with the help of the following script.
After “WHEN MATCHED THEN“, if the records match, we run the update statement.
After “WHEN NOT MATCHED BY TARGET THEN“, we perform the insert operation if the record in the source table is not in the target table.
After “WHEN NOT MATCHED BY SOURCE THEN“, we delete a record that is in the target table from the target table if it is not in the source table.
With OUTPUT statement, we produce the changes made as output.
|
1 2 3 4 5 6 7 8 9 10 11 |
MERGE INTO [dbo].[MyTableTarget] AS target USING [dbo].[MyTableSource] AS source ON (target.ID = source.ID) WHEN MATCHED THEN UPDATE SET [Name]=Source.[Name] WHEN NOT MATCHED BY TARGET THEN INSERT ([Name]) VALUES (source.[Name]) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action as [action], deleted.[Name], inserted.[Name]; |
