Playing with the MERGE statement in SQL Server 2008

The merge statement is a new DML feature that has been introduced with SQL Server 2008. The main purpose behind that feature is synchronizing two distinct data tables given that they both have same structure, that means they have both the same number and order of columns in addition to the column type. Say that we have two defined tables with SQL Server given data base as bellow:

USE tempdb 
 --Source data table
CREATE TABLE [Source]
(id INT NOT NULL IDENTITY(0,1),
content NVARCHAR(10)
)
PRINT 'Source is created'
--Destination data table
CREATE TABLE [Destination]
 (id INT NOT NULL IDENTITY(0,1),
content NVARCHAR(10)
)PRINT 'Destination is created'

As one can note, the both tables have same structure an identifier type of int and a content type of string of ten characters length

Now, if we try to populate them differently as follow

INSERT INTO [Source](content) VALUES('one'),('two'),('three'),('four')

INSERT  INTO [Destination](content) VALUES('un'),('deux'),('trois'),('quatre'),('cinq')

The first  table will hold the fourth first digits in english, meanwhile, the second table will hold the fifth first digits but in french. Then we try to employ the MERGE statement so that we synchonize the both tables. That means that the destination table will hold the same content of the first table, we proceed as follow

--MERGE statementMERGE INTO [Destination] AS t
USING [Source] AS s ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.content = s.content
WHEN NOT MATCHED BY SOURCE THEN
         DELETE;
GO

Recall that the MERGE Statement is always achieved by a semi colon