RSS

MERGE statement in SQL Server 2008

26 Dec

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

  • Conditionally insert or update rows in a single target table using a single statement which reduce number of code lines needed to perform that
  • Synchronize two tables specially in Data warehouse.

    Merge Syntax

    1.  
    2. The MERGE syntax consists of five primary clauses:

    3. The MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.
    4. The USING clause specifies the data source being joined with the target.
    5. The ON clause specifies the join conditions to determine where the target and source match.
    6. 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.

    Example

    1. Create Folder named db on c:\
    2. Create New Database Named TestDb
    3. Create New table named SrcTable
    4. Create New other table named DestTable
    5. Insert New Records in SrcTable
    6. Insert New Records in DestTable
    7. 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

     

     

  • Advertisements
     
    Leave a comment

    Posted by on December 26, 2009 in SQLServer, T-SQL, Warehouse

     

    Tags: , ,

    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

     
    %d bloggers like this: