SQL MERGE with variables

merge, sql-server, t-sql

Solution

You can use the `VALUES` clause to make a single row derived table then the rest is as usual.

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);

You might want to read Use Caution with SQL Server's MERGE Statement as well though.

Problem

How can I use the merge function with @variables in Microsoft SQL Server Management Studio 2008 r2? All of the examples that I have searched for online use tables to merge into tables. Is this possible with variables? For Example: ``` CREATE PROCEDURE UpdateOrder @id int, @payment_date smalldatetime, @amount numeric(10,2) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; MERGE order AS o USING @id, @payment_date, @amount ON @id = o.id WHEN MATCHED THEN UPDATE SET o.payment_date = @payment_date, o.amount = @amount o.last_updated_on = GETDATE() WHEN NOT MATCHED THEN INSERT(o.id,o.payment_id,o.amount) VALUES(@id,@payment_id,@amount); ``` This does not work. Thanks!

Original source