Click here to Skip to main content
15,868,159 members
Articles / Database Development / SQL Server / SQL Server 2008

Merge Statement in SQL Server 2008

Rate me:
Please Sign up or sign in to vote.
4.68/5 (20 votes)
10 Jun 2009CPOL 156.1K   26   10
Microsoft SQL Server 2008 new feature, Merge Statement

Introduction

One of the fantastic new features of SQL Server 2008 is Merge Statement. Using a single statement, we can Add/Update records in our database table, without explicitly checking for the existence of records to perform operations like Insert or Update.

Facts about Merge Statement

Here are a few facts that you must know before starting to use Merge Statement:

  1. Atomic statement combining INSERT, UPDATE and DELETE operations based on conditional logic
  2. Done as a set-based operation; more efficient than multiple separate operations
  3. MERGE is defined by ANSI SQL; you will find it in other database platforms as well
  4. Useful in both OLTP and Data Warehouse environments
    OLTP: merging recent information from external source
    DW: incremental updates of fact, slowly changing dimensions.

A typical merge statement looks like:

MERGE [INTO] <target table>
USING <source table or table expression>
ON <join/merge predicate> (semantics similar to outer join)
WHEN MATCHED <statement to run when match found in target>
WHEN [TARGET] NOT MATCHED <statement to run when no match found in target>

Example

SQL
-- Update existing, add missing
MERGE INTO dbo.tbl_Customers AS C
USING dbo.tbl_CustomersTemp AS CT
        ON C.CustID = CT.CustID
WHEN MATCHED THEN
    UPDATE SET
      C.CompanyName = CT.CompanyName,
      C.Phone = CT.Phone
WHEN NOT MATCHED THEN 
      INSERT (CustID, CompanyName, Phone)
      VALUES (CT.CustID, CT.CompanyName, CT.Phone)
SQL
CREATE TABLE dbo.tbl_Source (id INT, name NVARCHAR(100), qty INT);
CREATE TABLE dbo.tbl_Target (id INT, name NVARCHAR(100), qty INT);

--Synchronize source data with target
MERGE INTO dbo.tbl_Target AS t
    USING dbo.tbl_Source AS s    
        ON t.id = s.id
    WHEN MATCHED AND (t.name != s.name OR t.qty!= s.qty) THEN
        --Row exists and data is different
        UPDATE SET t.name = s.name, t.qty = s.qty
    WHEN NOT MATCHED THEN 
        --Row exists in source but not in target
        INSERT VALUES (s.id, s.name, s.qty) 
    WHEN SOURCE NOT MATCHED THEN 
        --Row exists in target but not in source
        DELETE OUTPUT$action, inserted.id, deleted.id

Conclusion

So now with this new feature, we can implement the feature of add/insert/delete using a single statement without checking through the records.

Hope you enjoyed this article. Happy programming!!!

History

  • 11th June, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
QuestionInsert , update rowcount in mege statememnt Pin
vipin jha11-Feb-15 0:40
vipin jha11-Feb-15 0:40 
Hi Thanks for your post ,

Similarly I have created a sp which is inserting and updating the records.
Now I want to track the count of new record inserted and updated record in @@rowcount .

please suggest me the code .
below is my sp
alter Procedure SP_Archive_using_merge
AS
--exec SP_Archive
BEGIN
SET NOCOUNT ON
Declare @Source_RowCount int
Declare @New_RowCount int
DECLARE @TimeIn SMALLDATETIME
DECLARE @LatestVersion INT

SET NOCOUNT ON

---BBxKey and Hash value of all the source columns are derived in source query itself--

select @TimeIn=getdate(),@LatestVersion=1

MERGE Archive.dbo.ArchiveBBxCemxr AS stm
USING (
SELECT a.*,cast(SUBSTRING(a.Col001,1,10) as varchar(100)) BBxKey,
HashBytes('MD5', CAST(CHECKSUM(a.Col001,a.Col002,a.Col003,a.Col004,a.Col005,a.Col006,a.Col007) AS varbinary(max))) RowChecksum,
b.BBxKey as Archive_BBxKey, b.RowChecksum as Archive_RowChecksum
FROM dbo.ImportBBxCemxr a LEFT OUTER JOIN Archive.dbo.ArchiveBBxCemxr b
ON a.Col001 = b.BBxKey
Where (b.LatestVersion = 1 OR b.LatestVersion IS NULL) AND a.Col001 IS NOT NULL

) AS sd
ON sd.Archive_BBxKey = stm.BBxKey and sd.RowChecksum = stm.RowChecksum

WHEN MATCHED AND (stm.BBxKey = sd.Archive_BBxKey and stm.RowChecksum != sd.Archive_RowChecksum) THEN

UPDATE SET

stm.TimeIn = @TimeIn,
BBXKey=sd.BBXKey,
RowChecksum=sd.RowChecksum,
stm.Col001=sd.Col001,
stm.Col002=sd.Col002,
stm.Col003=sd.Col003,
stm.Col004=sd.Col004,
stm.Col005=sd.Col005,
stm.Col006=sd.Col006,
stm.Col007=sd.Col007,
stm.LatestVersion=@LatestVersion

WHEN NOT MATCHED and (sd.Archive_BBxKey is null) THEN
Insert (TimeIn,BBXKey,RowChecksum,Col001,Col002,Col003,Col004,Col005,Col006,Col007,LatestVersion)
values(getdate(),sd.BBXKey,sd.RowChecksum,sd.Col001,sd.Col002,sd.Col003,sd.Col004,sd.Col005,sd.Col006,sd.Col007,@LatestVersion);

end

Regards,
Vipin jha
QuestionDrawbacks of Merge Pin
Member 1108063013-Sep-14 17:39
Member 1108063013-Sep-14 17:39 
GeneralMy vote of 3 Pin
Member 782653028-Oct-13 20:41
Member 782653028-Oct-13 20:41 
QuestionMissing semicolon Pin
M S Chaitanya9-May-13 4:26
M S Chaitanya9-May-13 4:26 
GeneralMy vote of 5 Pin
Schehaider_Aymen3-Oct-12 6:31
Schehaider_Aymen3-Oct-12 6:31 
GeneralMy vote of 3 Pin
Morteza Azizi23-Jan-12 3:05
professionalMorteza Azizi23-Jan-12 3:05 
GeneralRe: My vote of 3 Pin
Maciej Los8-May-12 10:32
mveMaciej Los8-May-12 10:32 
GeneralSample code invalid Pin
ryancrawcour1-Feb-10 14:15
ryancrawcour1-Feb-10 14:15 
GeneralMy Vote of 5 Pin
johnclark6410-Aug-09 17:05
johnclark6410-Aug-09 17:05 
GeneralRe: My Vote of 5 Pin
Robin_Roy17-Aug-09 15:52
Robin_Roy17-Aug-09 15:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.