Using output to set a variable in a merge statemen

问题:

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. I hope I'm just missing a some syntax.

回答1:

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