What is new in SQL Server 2008 aka Katmai
MERGE t-sql command enhancement in SQL Server 2008

SQL Server 2008 aka Katmai is released recently as the June 2007 CTP (Community Technology Preview) by Microsoft.
Katmai or the new version of SQL Server has enhancements also in the T-SQL usage of the database engine.
One of the t-sql improvements in SQL Server 2008 is the MERGE t-sql command. By using the Merge command you can compare two tables and update matched ones, or insert unmatched rows from one into other, or delete unmatched ones from the primary table.
Here, you can find a sample which you can run on a SQL Server 2008 database to view the results of MERGE command. Start first creating our tables which we will use and compare in the MERGE t-sql command syntax.

CREATE TABLE ExistingData
(
id int,
characters nvarchar(50)
)
GO

CREATE TABLE NewData
(
id int,
newcharacters nvarchar(50)
)
GO

INSERT INTO NewData VALUES (1, 'Darth Vader'), (2, 'Commander Bakara')
GO

This is the NewData table with its rows

Then if you run the below Merge query, you will see that all the records in the new table or staging table is inserted to the primary table since it is empty. Because there is not any rows in the primary table, there is no match so insert statement runs in this situation

MERGE ExistingData
USING
(
SELECT
id,
newcharacters
FROM NewData
) NewData ON ExistingData.id = NewData.id
WHEN MATCHED THEN UPDATE SET ExistingData.Characters = NewData.NewCharacters
WHEN TARGET NOT MATCHED THEN INSERT VALUES (id, NewCharacters)
WHEN SOURCE NOT MATCHED THEN DELETE;
GO

This is the ExistsingData table with its rows

Let's change the data now on both tables and run the Merge t-sql command to see what happens now.

UPDATE NewData SET NewCharacters = N'Commander Thire' WHERE id = 2
INSERT INTO NewData VALUES (3, N'Stass Allie'), (4, N'Chewbacca')
INSERT INTO ExistingData VALUES (5, N'Kit Fisto'), (6, N'Gree Commander')

ExistingData table	NewData table

What we are expecting now is updating the primary table values with the secondary table values where the id's of both side match. So, we can expect an update on the ExistingData table at row with id equals to 2. So Commander Thire will replace Commander Bakara

Also we can expect that the merge statement will cause new records in the primary table where id's of the secondary table does not exists among the id's of the primary table. So in the NewData table rows with id is 3 and 4 will be inserted into the primary table ExistingData during the merge process.

Last part of the Merge command is where SOURCE NOT MATCHED portion. Here the DELETE command will cause the rows with 5, and 6 will be deleted from the primary table.

So, in short, running the MERGE command as shown above will create a copy of the secondary table on primary table. Let's run and see the results.

As you see the ExistingData table has been altered as shown aside.

If we rerun the last step, last MERGE command commenting the line SOURCE NOT MATCHED then we can prevent deleting unmatched rows in the primary table. This way, we can merge records or new rows from the secondary table and update matched ones with new definitions but also keep the existing data in the primary table which is now coexisting in the secondary one. Here is the MERGE command to succeed this task:

MERGE ExistingData
USING
(
SELECT
id,
newcharacters
FROM NewData
) NewData ON ExistingData.id= NewData.id
WHEN MATCHED THEN UPDATESET ExistingData.Characters= NewData.NewCharacters
WHEN TARGET NOT MATCHED THEN INSERT VALUES (id, NewCharacters);
--WHEN SOURCE NOT MATCHED THEN DELETE;
GO

And here is the result of the above Merge statement on the ExistingData table, or the primary table.

BlinkList   Del.icio.us   Digg   Furl   Simpy   Spurl   DZone   ma.gnolia   Shadows