TechAid24

Your Technical Consultant

Create MERGE statement with variable in SQL Server

Posted on by Selim Ahmed 2 minutes read

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;

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 

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);
Create a MERGE statement with a variable in SQL Server

Tags:

Selim Ahmed

Thank you for visiting my personal blog. I’m Selim Ahmed, a tech-savvy who loves to learn a new technology every day. Innovation, research, and development is my daily activities. I love to work in a place where I can learn, innovate, and develop new things.Please feel free to reach me on any comments and feedback you have. I would be more than glad to listen and reply.

Leave a Reply

Your email address will not be published. Required fields are marked *