Pass auto generated id to other table

Question

So I will try to explain my problem with pseudo code. But first of all some explanation. I am trying to create simple procedure in SQL that will insert data into three tables, where IDs from table_1 and table_2 have to be passed to table_3. Not sure how to achieve it. I am using SQL Server.

pseudo code


CREATE PROCEDURE Bla
 @arg VARCHAR(1)
AS
  INSERT INTO table_1 VALUES(...) -- need id from here
  INSERT INTO table_2 VALUES(...) -- and id from here

  INSERT INTO table_3 VALUES (..., table_1_id, table_2_id)

As you can guess those IDs are Foreign Keys in table_3

So I was thinking about doing something like this, after I INSERT data into table_1 and table_2


@tab1id = SELECT TOP 1 id from table_1 ORDER BY id DESC
@tab2id = SELECT TOP 1 id from table_2 ORDER BY id DESC

INSERT INTO table_3 VALUES (..., @tab1id, @tab2id)

But for some reason I do not think it is good practice. So I googled it and found something like SCOPE_IDENTITYbut not sure how to use it properly, my idea is to do something like:


 INSERT INTO table_1 VALUES(...) 
 @tab1id = SELECT SCOPE_IDENTITY()

 INSERT INTO table_2 VALUES(...)
 @tab2id = SELECT SCOPE_IDENTITY()

 INSERT INTO table_3 VALUES (..., @tab1id, @tab2id)

But not sure if that is good idea, please give some advice.

Yes, your solutions is right. this is exactly how it's done. What would have been a problem would be if you used @@identity, which can give you a false result due to an insert from a trigger, ..., but scope_identity is safe. — Marc Guillot, Feb 13 '20 at 09:32
Thank you @MarcGuillot I will use that solution then. I was reading about `@@identity` and `scope_identity()` that why I came with solution, thanks a lot. — noname, Feb 13 '20 at 09:42

score 0 · Answer 1 · edited Mar 28 '20 at 10:08

When first table Id passes in other tables you have to check Id exist then Update your record if Id is not available then insert your record because of the SCOPE_IDENTITY every time increase Ids and you store it 3 table means foreign key.

When your stored procedure is executed the first time, then ID will change also 3rd table id also change means every time your id will be changed, so you need to check id is exist in your table then update your stored procedure and id is not exist in your table then update your record.

Set the trigger before insert and check the id is exist or not.

score 0 · Answer 2 · answered Feb 13 '20 at 10:53

You are on the right track.

As you can see in the accepted answer on the following topic: Best way to get identity of inserted row? , SCOPE_IDENTITY() returns values from your current scope and session.

The only thing that we should add is some kind of control, so we are sure that we not entering wrong information in table_3. Based on your code, please find SP body example below:

    DECLARE @tab1id int
    DECLARE @tab2id int
    DECLARE @ErrorVar int
    DECLARE @RowCountVar int

    BEGIN TRANSACTION

            INSERT INTO table_1 VALUES(...)
            --checking for errors:
            SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT
            IF (@ErrorVar > 0 OR @RowCountVar < 1)
                BEGIN
                    RAISERROR ('P1: Insert to table table_1 failed', 16, 1)
                    GOTO ErrExit
                END
            SET @tab1id = SCOPE_IDENTITY()

            INSERT INTO table_2 VALUES(...)
            --checking for errors:
            SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT
            IF (@ErrorVar > 0 OR @RowCountVar < 1)
                BEGIN
                    RAISERROR ('P1: Insert to table table_2 failed', 16, 1)
                    GOTO ErrExit
                END
            SET @tab2id = SCOPE_IDENTITY()

             INSERT INTO table_3 VALUES (..., @tab1id, @tab2id)
             --checking for errors:
            SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT
            IF (@ErrorVar > 0 OR @RowCountVar < 1)
                BEGIN
                    RAISERROR ('P1: Insert to table table_3 failed', 16, 1)
                    GOTO ErrExit
                END
            --if you need newly created table_3 IDENTITY 
            SET @tab3id = SCOPE_IDENTITY()

    --Normal exit
    COMMIT TRANSACTION

    --Error Exit
    ErrExit:
    ROLLBACK TRANSACTION

Pass auto generated id to other table

2 Answers2