MERGE Statement With Variable In SQL Server

In SQL Server 2008, you can perform insert, update, or delete operations in a single statement using the MERGE statement.

The MERGE statement allows you to join a data source with a target table or view, and then perform multiple actions against the target based on the results of that join. For example, you can use the MERGE statement to perform the following operations:

Conditionally insert or update rows in a target table. If the row exists in the target table, update one or more columns; otherwise, insert the data into a new row.
Synchronize two tables. Insert, update, or delete rows in a target table based on differences with the source data.


MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

MERGE target_table USING source_table

ON merge_condition

WHEN MATCHED

THEN update_statement

WHEN NOT MATCHED

THEN insert_statement

WHEN NOT MATCHED BY SOURCE

THEN DELETE;

The MERGE syntax consists of five primary clauses:

The MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.
The USING clause specifies the data source being joined with the target.
The ON clause specifies the join conditions that determine where the target and source match.
The WHEN clauses (WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE) specify the actions to take based on the results of the ON clause and any additional search criteria specified in the WHEN clauses.
The OUTPUT clause returns a row for each row in the target that is inserted, updated, or deleted.


-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' THEN 
    INSERT(EmployeeID, EmployeeName) 
    VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED THEN
    UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO

-- MERGE statement with the join conditions specified correctly.

USE tempdb;

BEGIN TRAN;

MERGE Target AS T

USING Source AS S

ON (T.EmployeeID = S.EmployeeID)

WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' THEN

INSERT(EmployeeID, EmployeeName)

VALUES(S.EmployeeID, S.EmployeeName)

WHEN MATCHED THEN

UPDATE SET T.EmployeeName = S.EmployeeName

WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'

THEN DELETE

OUTPUT $action, inserted.*, deleted.*;

ROLLBACK TRAN;

I want to use the merge function with @variables in Microsoft SQL Server Management Studio 2008 r2.


MERGE ORDER AS O
USING (VALUES (@id,@payment_date,@amount)) AS S(id, payment_date, amount)
ON S.id = O.id
WHEN MATCHED THEN
  UPDATE SET O.payment_date = S.payment_date,
             O.amount = S.amount,
             O.last_updated_on = GETDATE()
WHEN NOT MATCHED THEN
  INSERT(O.id,O.payment_id,O.amount)
  VALUES(S.id,S.payment_id,S.amount);