The MERGE Statement in SQL Server 2008

The MERGE Statement is one of the new T-SQL enhancements in SQL Server 2008. So what actually is the MERGE Statement? When and how should it be used?

The MERGE Statement actually allows joining a source with a target table and then based on the results of the join, it performs insert, update, or delete operations on the target table.

A simplified syntax example of the MERGE Statement is the following:

MERGE [tableA] as target using [tableB] as source on target.id = source.id
when matched then update set target.col1 = source.col1, target.col2 = source.col2
when not matched then insert values (source.id, source.col1, source.col2)
when not matched by source then delete;

So let’s analyze the above syntax. I will use some steps for that.

Step 1: Declare your source and target tables.

Step 2: Declare a joining condition on the two tables (in our example is the target.id = source.id condition).

Step 3: Set the action for the when matched case. This case means that a record was found that exists in both tables. To this end, you should perform an update in order to synchronize the two records.

Step 4: Set the action for the when not matched case. This case means that a record was found in the source table which does not exist in the target table. To this end, you should perform an insert in order to copy the record from the source to the target.

Step 5: Set the action for the when not matched by source case. This case means that a record was found in the target table which does not exist in the source table. To this end, you can delete this record (if the purpose of this operation is the synchronization of the two tables).

Please note that the MERGE statement should always end with a semicolon (;).

Read more on this article...

TechEd EMEA Developers 2008

Today it was the last day of Tech·Ed EMEA Developers 2008 in Barcelona, Spain.

The conference took place between the 10th and 14th November 2008. It was my first time in such big event and I have to admit that I was excited! Thousands of developers around Europe and other countries near Europe, huge amounts of knowledge exchange, very interesting sessions on new Microsoft technologies, ask-the-experts sessions where someone could have deep technical discussions with Microsoft experts regarding the various MS technologies, exhibitions and of course, Speaker Idol!

Based on the fact that I wanted to experience TechEd as much as I could, I participated to the Speaker Idol Contest :). Even though I was somehow expecting to feel a little bit nervous I did not; after all I was among fellow developers!

So, I talked about the MERGE statement in SQL Server 2008; a really interesting T-SQL enhancement. The presentation went well but unfortunately I did not make it to the finals. Though, regardless the result, the experience was worth every minute!

With these great emotions, I am leaving Barcelona tonight and hopefully I will make it to the next TechEd in Berlin next year. Who knows? I might even participate again to Speaker Idol! :)

Read more on this article...