SQL Merging DATA

:::SQL MERGE

SQL server 2008 has a new feature to merge tables with actions for different conditions.

Here is an example for your reference:

MERGE INTO dbo.Target as TT
using dbo.Source as SS
    on TT.keyid = SS.keyid
WHEN MATCHED THEN
   UPDATE SET
     TT.col1 = SS.col1,
     TT.col2  = SS.col2,
     TT.col3 = SS.col3,
 WHEN NOT MATCHED THEN
  INSERT (col1,col2, col3,colX,colY )
  values (SS.col1,SS.col2,SS.col3,SS.colX,SS.colY);

--you need to use semicolon to finish the statement!

You can use merge statement to insert new table data to the existing table. You can also do the same operation with multiple insert, update , delete statements.

If you want to specify multiple conditions for matched case, you can do that.


MERGE INTO dbo.Target as TT
using dbo.Source as SS
 on TT.keyid = SS.keyid
 WHEN MATCHED AND (
  TT.col1 <> SS.col1
  OR TT.col2 <> SS.col2 )  THEN
UPDATE SET
     TT.col1 = SS.col1,
     TT.col2 = SS.col2
  WHEN NOT MATCHED THEN
     INSERT (col1,col2, col3,colX,colY )
        values (SS.col1,SS.col2,SS.col3,SS.colX,SS.colY);

You can update the related record, if it justifies your condition. You can monitor transactions with this statement. If there is a change in the record, you can add that to audit records.

:::HOW TO DELETE with TOP Option and USE ordering

Well, you might want to delete top 10 product reviews based on Ordering of low ratings. Ordering will not work directly. A logical ORDER BY for the top option in a modification statement is not allowed. When you say :

::>Delete top 10 from dbo.myreviews;

It will delete arbitrary records. if you want to order them, you can use table expressions.

WITH C as
(
   select top( 10 ) *
   from dbo.myreviews
   order by rating asc
)
DELETE FROM C;

Now, you got rid of the bad ratings for your product.