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?

You can use inserted.id anyway whatever the column in the target table is auto increment primary key or not — Denis Rubashkin
– Denis Rubashkin, Commented Dec 13, 2018 at 8:13

Zohar Peled · Accepted Answer · 2018-12-13 08:11:58Z

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

Damien_The_Unbeliever · Accepted Answer · 2018-12-13 08:01:04Z

I cannot give value for id because it is the auto increment primary key.

No it isn't. You haven't declared it to be anything of the sort. So we need to fix that first:

declare @txn table (
    id bigint IDENTITY PRIMARY KEY,
    description varchar(max),
    txn_no varchar
);

And then we fix it by specifying a column list in your INTO clause:

output
    inserted.description,
    inserted.txn_no
into @txn (description, txn_no)

It's always a good habit to specify column lists anyway.

Or if I've misinterpreted your question, and the id should be coming from transactions, then you just add inserted.id as another column in your OUTPUT clause. inserted represents that state of the table after the insert. So you can include columns from it in your OUTPUT clause even if you didn't specify them in the INSERT.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related