Getting ID into temp table variable on INSERT INTO ... OUTPUT ... INTO temp table

Question

This is the sample procedure I am using.

create procedure PRO_ProcName
    @description varchar(max), 
    @txn_no varchar
as
begin
    declare @txn table (
        id bigint,
        description varchar(max),
        txn_no varchar
    );

    declare @txn_id bigint;

    insert into transactions    
    (
        description,
        txn_no
    )
    output
        inserted.description,
        inserted.txn_no
    into @txn
    values
    (
        @description,
        @txn_no
    )

    select @txn_id = id from @txn;
end

I am getting an error like:

Column name or number of supplied values does not match table definition.

I know that it is because I have id field in my temporary table and it is not getting inserted in the insert into statement. I cannot give value for id because it is the auto increment primary key.

How can I tackle this situation and get id of inserted record into a variable?

Answer 1

The inserted table represents the data that exists in the target table after the insert - so it also includes the auto-generated values, whether they where generated by a default value definition or by an identity definition on the columns - so you need to add inserted.id to the output clause.

However, there are two more things wrong in your procedure.
The first and most important is the fact that you didn't specify a length to the @txn_no varchar parameter. SQL Server will implicitly specify the length of 1 char in this case.
The second is the fact that you are not specifying the columns list of @txn in the output clause.

Here is a improved version of your code with all these issues fixed:

create procedure PRO_ProcName
    @description varchar(max), 
    @txn_no varchar(255) -- Note: I don't know the actual length you need
as
begin
    declare @txn table (
        id bigint,
        description varchar(max),
        txn_no varchar
    );

    declare @txn_id bigint;

    insert into transactions    
    (
        description,
        txn_no
    )
    output
        inserted.id,
        inserted.description,
        inserted.txn_no
    into @txn(id, description, txn_no)
    values
    (
        @description,
        @txn_no
    )

    select @txn_id = id from @txn;
end