Using output to set a variable in a merge statement

Question

I have a merge statement that should update or insert a single record always. I want to remember the ID of that statement in a variable. It looks like this:

DECLARE @int int

MERGE dbo.table AS A
USING (SELECT 'stringtomatch' AS string) AS B ON B.string= A.string
WHEN MATCHED THEN 
    UPDATE SET somecolumn = 'something'
WHEN NOT MATCHED THEN
    INSERT 
    VALUES ('stringtomatch',
        'something')
OUTPUT @int = inserted.ID;

Now this doesen't work because you can't set @int in the output clause this way. I know I could create a temptable and use INTO @temptable in the output. But since I know it's always a single record I want to have the ID in a INT variable. Is this even possible? Or am I forced to use a table variable.

How?

score 31 · Accepted Answer · edited Jun 20 '20 at 09:12

31

No, you have to use a table variable with OUTPUT

However, you can do this...

...
WHEN MATCHED THEN 
    UPDATE
    SET
       @int = ID,
       somecolumn = 'something'
WHEN NOT MATCHED THEN
    INSERT 
    VALUES ('stringtomatch',
        'something');

SET @int = ISNULL(@int, SCOPE_IDENTITY());

The "assign in UPDATE" has been a valid syntax for SQL Server for a long time. See MERGE on MSDN too. Both say this:

...
<set_clause>::=

SET
...
@variable=expression

edited Jun 20 '20 at 09:12

Community

1
1

answered May 30 '13 at 14:26

gbn

422,506
82
585
676

Thx for the straight answer and alternative. Didn't know about the assign in UPDATE, thx allot for the info. Will accept as answer as soon as I can, since your so fast :P – Edwin Stoteler May 30 '13 at 14:32

score 0 · Answer 2 · answered Jun 14 '23 at 17:40

Know this is ten years old but I came across it and thought I would add another answer. The problem with the selected answer is it's not idempotent. It will only work for inserts and not updates from what I have seen. You can use the built in 'OUTPUT' like you were doing. But just bite the bullet and make a table of one column of the type you want and then get a max of that.

DECLARE @A Table ( ID INT, VAL CHAR);
DECLARE @B Table ( ID INT, VAL CHAR);
DECLARE @Results TABLE ( ID INT );
DECLARE @Id INT;

SET NOCOUNT ON;
INSERT INTO @A VALUES (1, 'A');

MERGE @B AS T
USING @A AS S ON T.Id = S.Id
WHEN MATCHED
    THEN UPDATE SET T.VAL = S.VAL
WHEN NOT MATCHED
    THEN INSERT (ID, VAL) VALUES (S.ID, S.VAL)
OUTPUT inserted.Id
INTO @Results
;

SELECT @Id = MAX(Id) FROM @Results

SELECT @Id

Using output to set a variable in a merge statement

2 Answers2

Linked