SQL Server: Use output from first insert to insert into second table

Question

I have a basic stored procedure that adds a new record to a table with a structure like the following. The table also has a column "itemID" that is set to auto-incrementing to create a unique id for each record.

I would like to use Output to get this id from the new record I inserted and then use this to add a new record to another table (Table2 with columns colD, colE, colF). colF in Table2 should be the Output from the below as this is the id that links both tables.

Can someone here tell me how this would work as I am pretty new to SQL and have never done this before ?

My stored procedure (example):

ALTER PROCEDURE [dbo].[CreateStuff]
    @colA datetime,
    @colB varchar(50),
    @colC nvarchar(20)
AS
BEGIN
SET NOCOUNT ON;
    INSERT INTO Table1
        (
            colA,
            colB,
            colC
        )
    SELECT  @colA,
            @colB,
            @colC
END

Many thanks for any help with this, Tim.

no i think output clause is ideal scenario.just google it for syntax — KumarHarsh
– KumarHarsh, Commented Feb 22, 2014 at 14:29

Dale K · Accepted Answer · 2021-11-30 21:48:51Z

3

BEGIN  
    SET NOCOUNT ON;

    /* Here declare a Table Variable */

    DECLARE @Table_Var TABLE(itemID INT)

    INSERT INTO Table1(colA,colB,colC)
    OUTPUT inserted.itemID INTO @Table_Var(itemID)
    SELECT @colA,@colB,@colC
    
    
    /* Now do the insert into Table 2*/        
    INSERT INTO TABLE2
    SELECT itemID FROM @Table_Var
END

SCOPE_IDENTITY() is only good when you are doing a single Insert, and it is an IDENTITY column whos value you want to capture. It will only return the last Generated Identity value.

Other then that if you are doing multiple insert or it isn't an identity column then you should use OUTPUT clause along with a table variable/temp table to capture the inserted values. and then do whatever you want to do with them values later on (insert in another table/logging whatever).

To learn more about OUTPUT Clause have a look at this link.

edited Nov 30, 2021 at 21:48

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Feb 22, 2014 at 14:33

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2571510 Over a year ago

Where would I declare the other input for Table2 ? If I declare them together with the ones for Table1 above AS BEGIN than I get an error that the number of SELECT and INSERT values does not match.

M.Ali Over a year ago

Have a look now I have chnaged the name to table variable to @Table_Var , this is the table you will declare before the very 1st insert then you will use the OUTPUT cluase in your INSERT statement to populate this table variable. Now when yor insert into Table2 (the table you have mentioned in your question), you will INSERT into table2 selecting values from the table variable.

user2571510 Over a year ago

colD, colE, colF whereas colF is the one that contains the id (output) from Table1.

Milen · Accepted Answer · 2014-02-22 14:24:59Z

2

Try with SCOPE_IDENTITY():

ALTER PROCEDURE [dbo].[CreateStuff]
    @colA datetime,
    @colB varchar(50),
    @colC nvarchar(20),
    @Valueout int output
AS
BEGIN
SET NOCOUNT ON;
    INSERT INTO Table1
        (
            colA,
            colB,
            colC
        )
    SELECT  @colA,
            @colB,
            @colC
SET @Valueout = SCOPE_IDENTITY()
END

answered Feb 22, 2014 at 14:24

Milen

8,8977 gold badges47 silver badges59 bronze badges

3 Comments

user2571510 Over a year ago

Thanks for the quick reply - this looks interesting ! Can I do the insert into Table2 in the same procedure ?

OGHaza Over a year ago

@user2571510 You can indeed

user2571510 Over a year ago

And how ? Just adding it below ?

Wasim · Accepted Answer · 2014-02-22 14:26:09Z

1

You can create a Trigger on the table as follows:

    CREATE TRIGGER Trigger_Name 
       ON  Table_Name 
       AFTER INSERT,UPDATE
    AS 
    BEGIN
       SET NOCOUNT ON;

       INSERT INTO Table1 ( colA, colB, colC )
       SELECT  colA, colB, colC FROM Inserted

    END

answered Feb 22, 2014 at 14:26

Wasim

1,17211 silver badges25 bronze badges

2 Comments

user2571510 Over a year ago

Thanks for the quick reply. What is Inserted in that case ?

Wasim Over a year ago

Inserted is a Keyword of SQL server what points the Table_name where you inserted.

Collectives™ on Stack Overflow

SQL Server: Use output from first insert to insert into second table

3 Answers 3

3 Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related