Using output to set a variable in a merge statement

Question

I have a merge operator that should update or insert one record always. I want to remember the identifier of this operator 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 does not work, because you cannot set @int in the output condition this way. I know that I can create seductive and use INTO @temptable in the output. But since I know , there is always one record that I want to have an ID in an INT variable. Is it possible? Or am I forced to use a table variable. Hope I just skipped some syntax.

Answer 1

No, you need to use a table variable with OUTPUT

However, you can do it ...

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

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

...
<set_clause>::=

SET
...
@ Variable = expression