Synchronizing two tables using MERGE statement

INSERT, UPDATE and DELETE in one single block of code.

Problem: Synchronizing two tables, when trying to write a query which checks whether if the target table exists or not and then based on the condition either insert, update or delete.

Solution:

MERGE (Transact-SQL), starting from SQL Server 2008.

 Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Example:

In this example I am trying to show how merge works to synchronize between two tables, also Audit the actions and inserted ID’s or deleted ID’s.

Source table and Target table are joined by the primary key or unique key which gives a row match to INSERT (identify whether it is a new record in the source and target doesn’t have this new one)

UPDATE (identify whether it is a updated record in the source and target doesn’t have this updates)

DELETE (identify whether it is a deleted in the source and target have this record)

In the below example I am using CallId which is the primary key and not null as my JOIN condition in between Source(S_Call) and Target(T_Call).

First I would check the matching Id’s and then check whether there is any change between this two tables to update the target using WHEN MATCHED AND (optional condition) THEN UPDATE.

Next I would like to check whether there is a new row by specify WHEN NOT MATCHED BY Target and (optional condition) THEN INSERT this will insert the values from Source to target

Next I would like to check whether there is a new row by specify WHEN NOT MATCHED BY Source and (optional condition) THEN DELETE this will delete the target rows to make identical to source.

Lastly I would like to know what all records are inserted, updated or deleted by writing the output to a table(AuditCall), $action will record the type of action performed.

CREATE TABLE [dbo].[S_Call]

(

                [CallId] [int] NOT NULL PRIMARY KEY,

                [CallerName] [varchar] (25) NULL,

                [Location] [varchar] (50) NULL,

                [Phone] [varchar] (25) NULL,

                [Status] [varchar] (20) NULL

)

GO

Sample Values for S_Call:

CREATE TABLE [dbo].[T_Call]

(

                [CallId] [int] NOT NULL PRIMARY KEY,

                [CallerName] [varchar] (25) NULL,

                [Location] [varchar] (50) NULL,

                [Phone] [varchar] (25) NULL,

                [Status] [varchar] (20) NULL

)

GO

Sample Values for T_Call:   

 

CREATE TABLE

dbo.AuditCall

(

                AuditCallID INT PRIMARY KEY Identity(1, 1)

                ,ActionCall VARCHAR(100)

                ,Inserted_call INT

                ,Deleted_call INT

)

GO

MERGE INTO dbo.T_Call AS T

USING (

                SELECT [CallId]

                                ,[CallerName]

                                ,[Location]

                                ,[Phone]

                                ,[Status]

                FROM dbo.S_Call

                ) AS S([CallId], [CallerName], [Location], [Phone], [Status])

                ON T.Callid = S.Callid

WHEN MATCHED AND  ((T.[CallerName] <> S.CallerName) OR (T.[Location] <> S.Location) OR (T.[Phone] <> S.Phone) OR (T.[Status] <> S.STATUS) )-- Updates based on this condition

                THEN

                                UPDATE

                                SET [CallerName] = S.CallerName

                                                ,[Location] = S.Location

                                                ,[Phone] = S.Phone

                                                ,[Status] = S.STATUS

WHEN NOT MATCHED BY TARGET --

                THEN

                                INSERT (

                                                [CallId]

                                                ,[CallerName]

                                                ,[Location]

                                                ,[Phone]

                                                ,[Status]

                                                )

                                VALUES (

                                                S.[CallId]

                                                ,S.[CallerName]

                                                ,S.[Location]

                                                ,S.[Phone]

                                                ,S.[Status]

                                                )

WHEN NOT MATCHED BY SOURCE

                AND T.CallID IS NOT NULL

                THEN

                                DELETE

OUTPUT $ACTION

                ,Inserted.CallID

                ,Deleted.CallID

INTO dbo.AuditCall

 (Actioncall, Inserted_call,Deleted_call);

Select * from dbo.AuditCall

Resultset :