Obtaining Auto-Generated Primary Key Values for Use as Foreign Keys

Question

I am running a local instance of Microsoft SQL Server 15 (2019) and am using Microsoft SQL Server Management Studio v18.9.1 as the client to work through the process of creating the tables needed for my application.

This is all running under Windows 10.

Tables

Suppose I have the tables defined below.

CREATE TABLE dbo."Table_1"
(
   "SomeData" VARCHAR(50) NOT NULL,
   "Table_1_SurrogateKey" BIGINT IDENTITY(1,1) NOT NULL

   CONSTRAINT PK_Table_1 PRIMARY KEY ("Table_1_SurrogateKey")
);

CREATE TABLE dbo."Table_2"
(
   "Table_2_PrimaryKey" VARCHAR(10) NOT NULL,
   "Table_1_SurrogateKey_A" BIGINT NOT NULL,
   "Table_1_SurrogateKey_B" BIGINT NOT NULL,

   CONSTRAINT PK_Table_2 PRIMARY KEY ("Table_2_PrimaryKey"),

   CONSTRAINT FK_Table_2_Table_1_SurrogateKey_A FOREIGN KEY ("Table_1_SurrogateKey_A") REFERENCES dbo."Table_1"("Table_1_SurrogateKey"),
   CONSTRAINT FK_Table_2_Table_1_SurrogateKey_B FOREIGN KEY ("Table_1_SurrogateKey_B") REFERENCES dbo."Table_1"("Table_1_SurrogateKey"),
   CONSTRAINT CHK_Table_1_SurrogateKey_A_NE_Table_1_SurrogateKey_B CHECK ("Table_1_SurrogateKey_A" != "Table_1_SurrogateKey_B")
);

A side note: Each row in "Table_2" always has two associated rows in "Table_1". Therefore, this is properly normalized.

INSERT Statements

Suppose I execute the INSERT statements shown below.

-- Primary keys for "Table_1" are auto-generated and thus do not appear explicitly in INSERT statements
INSERT INTO dbo."Table_1" ("SomeData") VALUES ('foo');
INSERT INTO dbo."Table_1" ("SomeData") VALUES ('bar');

-- Primary keys for "Table_2" are not auto-generated and thus must appear explicitly in INSERT statements
INSERT INTO dbo."Table_2" ("Table_2_PrimaryKey", "Table_1_SurrogateKey_A", "Table_1_SurrogateKey_B")
                          VALUES ('A16922', ???, ???);

As indicated by the ??? values given for the second and third fields of the last INSERT statement above, I am unsure how to determine the needed value of the auto-generated primary keys from the first two INSERT statements above.

Related Experience (postgres)

When working with postgres, I found the following technique to solve this problem:

INSERT INTO dbo."Table_2" ("Table_2_PrimaryKey", "Table_1_SurrogateKey_A", "Table_1_SurrogateKey_B")
                          VALUES (
                                    'A16922',
                                    currval('"Table_1_Table_1_PrimaryKey_seq"')-1,
                                    currval('"Table_1_Table_1_PrimaryKey_seq"')-0
                                 );

Here, table "Table_1_Table_1_PrimaryKey_seq" was automatically generated by postgres to track the sequence of primary keys that had been auto-generated.

This does not work when using Microsoft SQL Server. The following error is given:

Msg 515, Level 16, State 2, Line 91 Cannot insert the value NULL into column 'Table_1_SurrogateKey_A', table 'My_DB.dbo.Table_2'; column does not allow nulls. INSERT fails.

So, I am having difficulty finding the right way to do this in a SQL Server environment.

A Near Solution

SCOPE_IDENTITY() seems close to what I need. So, I tried the following:

INSERT INTO dbo."Table_2" ("Table_2_PrimaryKey", "Table_1_SurrogateKey_A", "Table_1_SurrogateKey_B")
                          VALUES (
                                    'A16922',
                                    SCOPE_IDENTITY()-1,
                                    SCOPE_IDENTITY()-0
                                 );

My Question (Microsoft SQL Server)

When working with Microsoft SQL Server, how may I obtain the auto-generated primary keys for the two rows inserted in "Table_1"? As can be seen, they are needed as foreign keys for insertion of a row into related table "Table_2".

Please note that the solution needs to be backwards-compatible with Microsoft SQL Server 13 (2016).

Answer 1

You are really close with your solution using SCOPE_IDENTITY(). However, your code assume no other transaction inserted a row in Table_1 between your 2 INSERTs. To avoid this issue, you could do something like this:

DECLARE @id1 INT;
DECLARE @id2 INT;
INSERT INTO dbo."Table_1" ("SomeData") VALUES ('foo');
SET @id1=SCOPE_IDENTITY();
INSERT INTO dbo."Table_1" ("SomeData") VALUES ('bar');
SET @id2=SCOPE_IDENTITY();

INSERT INTO dbo."Table_2" ("Table_2_PrimaryKey", "Table_1_SurrogateKey_A", "Table_1_SurrogateKey_B")
                          VALUES ('A16922', @id1, @id2);