SQL Server Output Clause into a scalar variable

Question

Is there any "simple" way to do this or I need to pass by a table variable with the "OUTPUT ... INTO" syntax?

DECLARE @someInt int

INSERT INTO MyTable2(AIntColumn)
OUTPUT @SomeInt = Inserted.AIntColumn
VALUES(12)

Answer 1

You need a table variable and it can be this simple.

declare @ID table (ID int)

insert into MyTable2(ID)
output inserted.ID into @ID
values (1)

Answer 2

Over a year later... if what you need is get the auto generated id of a table, you can just

SELECT @ReportOptionId = SCOPE_IDENTITY()

Otherwise, it seems like you are stuck with using a table.

Answer 3

Way later but still worth mentioning is that you can also use variables to output values in the SET clause of an UPDATE or in the fields of a SELECT;

DECLARE @val1 int;
DECLARE @val2 int;
UPDATE [dbo].[PortalCounters_TEST]
SET @val1 = NextNum, @val2 = NextNum = NextNum + 1
WHERE [Condition] = 'unique value'
SELECT @val1, @val2

In the example above @val1 has the before value and @val2 has the after value although I suspect any changes from a trigger would not be in val2 so you'd have to go with the output table in that case. For anything but the simplest case, I think the output table will be more readable in your code as well.

One place this is very helpful is if you want to turn a column into a comma-separated list;

DECLARE @list varchar(max) = '';
DECLARE @comma varchar(2) = '';
SELECT @list = @list + @comma + County, @comma = ', ' FROM County
print @list

Answer 4

Answer

You can use @@IDENTITY to get the last inserted id.

DECLARE @someInt int
INSERT INTO MyTable2(AIntColumn)
VALUES(12)
SET @someInt = @@IDENTITY;

Assuming your table has a primary key which you are looking to set as said variable.

Example schema

CREATE TABLE MyTable2(
    [ID] [INT] IDENTITY(1,1),
    [aIntColumn] [INT]
)
CREATE TABLE MyTable3(
    [ID] [INT] IDENTITY(1,1),
    [newInt] [INT],
    [FK_int] [INT]
)

Then you can use that in the next part of your script, e.g.

DECLARE @someInt INT;
INSERT INTO MyTable2(AIntColumn)
VALUES(12)
SET @someInt = @@IDENTITY;

--do something else
DECLARE @someInt2 INT;
INSERT INTO MyTable3(newInt, FK_int)
VALUES(101, @someInt)
SET @someInt2 = @@IDENTITY;

SELECT @someInt AS 'First Inserted ID ',  @someInt2 AS 'Second inserted ID';