Introduction
In SQL Server 2008, you can perform insert, update, or delete operations in a single statement using the MERGE statement.
Using of MERGE statement
Merge Syntax
- The MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.
- The USING clause specifies the data source being joined with the target.
- The ON clause specifies the join conditions to determine where the target and source match.
- The WHEN clauses specify the actions to take based on the results of the ON clause.
The OUTPUT clause returns a row for each row in the target object that is updated, inserted, or deleted.
The MERGE syntax consists of five primary clauses:
Example
- Create Folder named db on c:\
- Create New Database Named TestDb
- Create New table named SrcTable
- Create New other table named DestTable
- Insert New Records in SrcTable
- Insert New Records in DestTable
- Use Merge Statement to Synchronize DestTable to SrcTable which insert, update or delete from target table to map to source table
Example Code (Copy and Run script bellow on SQL Server Managment Studio)
USE [master]
GO
/********************************************
———-1)Create TestDb——————–
********************************************/
CREATE DATABASE [TestDb] ON PRIMARY
( NAME = N’TestDb_Data’, FILENAME = N’C:\db\TestDb_Data.mdf’ , SIZE = 174080KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
LOG ON
( NAME = N’TestDb_Log’, FILENAME = N’C:\db\TestDb_Log.ldf’ , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
GO
use [TestDb];
go
/********************************************
———-2)Create SrcTable—————–
********************************************/
CREATE TABLE [dbo].[SrcTable](
[SrcId] [int] NOT NULL,
[SrcName] [nvarchar](50) NULL
)
go
/********************************************
———-3)Create DestTable—————–
********************************************/
CREATE TABLE [dbo].[DestTable](
[DestId] [int]NOT NULL,
[DestName] [nvarchar](50) NULL
)
GO
/********************************************
——-4)Insert Records to SrcTable———-
********************************************/
Insert into SrcTable values(1,‘SrcTest1′)
Insert into SrcTable values(2,‘SrcTest2′)
Insert into SrcTable values(5,‘SrcTest3′)
go
/********************************************
——-5)Insert Records to DestTable———-
********************************************/
Insert into DestTable values(1,‘SrcTest1′)
Insert into DestTable values(2,‘DestTest2′)
Insert into DestTable values(3,‘DestTest3′)
Insert into DestTable values(4,‘DestTest4′)
go
/********************************************
6)Use Merge Statement to Syncronize DestTable to SrcTable
********************************************/
–View DestTable and DestTable before Merge
Select * From SrcTable
Select * From DestTable
go
–Target table which we want to perform on it insert, Update and delete statement
Merge DestTable as Target
–Source Table which we want to join it with Taget Table
using(Select SrcId,SrcName From SrcTable)as Source
–Join Condition between target and Source
on (Target.DestId=Source.SrcId)
when Matched and Target.DestName<>Source.SrcName then
Update Set Target.DestId=Source.SrcId, Target.DestName=Source.SrcName
when Not Matched by Target then
Insert (DestId,DestName) values(Source.SrcId,Source.SrcName)
When Not Matched by source then
delete
OUTPUT $action, Inserted.*, Deleted.*;
go
–View SrcTable and DestTable after Merge
Select * From SrcTable
Select * From DestTable
go
