Hi Dinesh,
I read your article and very impressed with the new features in SQL Server 2008. But, I am little bit confused with MERGE statement execution.
Let me explain:
You created two tables named tblSource and tblTarget. Both the tables have Identity columns.
In the second MERGE example which includes DELETE option. tblSource has IDs 1,2 and 3 and initially tblTarget table has IDs 1,2,3 and 4 out of which 1 and 2 IDs are matching with tblSource table but not 3 and 4. MERGE statement updates the ID = 2 in tblTarget table then INSERT the ID = 3 again into tblTarget table. Now the ID in tblTarget table should be 5, because already ID 3 is existing in tblTarget table. DELETE option deletes the IDs 3 and 4 from tblTarget table. Finally tblTarget should have IDs 1,2 and 5, but your Final Target table has IDs 1, 2 and 3. how is that possible?
Does the Identity columns fills the gaps in SQL Server 2008 or the precedence of the execution is different than what I am expecting?
Can you please clarify my doubt?
Regards,
Ravi.