Merge Statement in SQL Server 2008

In versions of SQL Server prior to SQL Server 2008, if you had a set of data rows in a source table that you wanted to synchronize with a target table, you had to perform at least three operations:

I. One scan of the source table to find matching rows to update in the target table,

II. Another scan of the source table to find non matching rows to insert into the target table,

III. And a third scan to find rows in the target table not contained in the source table that needed to be deleted.

SQL Server 2008, however, introduces the MERGE statement. With the MERGE statement, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table, all in just a single statement, minimizing the number of times that rows in the source and target tables need to be processed.

The MERGE statement can also be used for performing conditional inserts or updates of rows in a target table from a source table.

The basic syntax of the MERGE statement is as follows:

[ WITH common_table_expression [,...n] ]

MERGE

[ TOP ( N ) [ PERCENT ] ]

[ INTO ] target_table [ [ AS ] table_alias ]

USING table_or_view_name [ [ AS ] table_alias ]

ON merge_search_condition

[ WHEN MATCHED [ AND search_condition ]

THEN { UPDATE SET set_clause | DELETE } ] [ ...n ]

[ WHEN NOT MATCHED [ BY TARGET ] [ AND search_condition ]

THEN { INSERT [ ( column_list ) ] { VALUES ( values_list ) | DEFAULT VALUES}} ]

[ WHEN NOT MATCHED BY SOURCE [ AND search_condition ]

THEN { UPDATE SET set_clause | DELETE } ] [ ...n ]

[ OUTPUT column_name | scalar_expression

INTO { @table_variable | output_table } [ (column_list) ] ]

[ OUTPUT column_name | scalar_expression [ [AS] column_alias_identifier ] [,...n ] ] ;

The MERGE syntax consists of the following primary clauses:

1. The MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.

2. The USING clause specifies the data source being joined with the target. The ON clause specifies the join conditions that determine how the target and source match.

3. The WHEN MATCHED clause specifies either the update or delete operation to perform when rows of target table match rows in the source table and any additional search conditions.

4. WHEN NOT MATCHED BY TARGET specifies the insert operation when a row in the source table does not have a match in the target table.

5. WHEN NOT MATCHED BY SOURCE specifies the update or delete operation to perform when rows of the target table do not have matches in the source table.

6. The OUTPUT clause returns a row for each row in the target that is inserted, updated or deleted.

The WHEN clauses specify the actions to take on the rows identified by the conditions specified in the ON clause. The conditions specified in the ON clause determine the full result set that will be operated on. Additional filtering to restrict the affected rows can be specified in the WHEN clauses. Multiple WHEN clauses with different search conditions can be specified. However, if there is a MATCH clause that includes a search condition, it must be specified before all other WHEN MATCH clauses.

Example:

SELECT * FROM Test01

SELECT * FROM Test02

MERGE

  dbo.Test01

  AS TargetLV

USING (

        SELECT

          *

        FROM

          dbo.Test02

      ) AS SourceLV

  ON TargetLV.Id = SourceLV.Id

WHEN MATCHED THEN

  UPDATE

  SET

    TargetLV.Name = SourceLV.Name

WHEN NOT MATCHED BY SOURCE THEN

  DELETE

WHEN NOT MATCHED BY TARGET THEN

  INSERT

  (

    ID,

    Name

  )

  VALUES

  (

    SourceLV.ID,

    SourceLV.Name

)

OUTPUT $ACTION, Inserted.Id, Inserted.Name,Deleted.Id, Deleted.Name;

SELECT * FROM Test01

SELECT * FROM Test02

Conclusion

Generally, we can use the MERGE statement while implementing the Master-Child relationship. What I have tried to explain here is just an overview of this statement. For further details, you can refer –

http://msdn.microsoft.com/en-us/library/bb510625.aspx

http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/