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).
SCOPE_IDENTITY()-1,
Nope - don't even go down this path. You need to "save" the identity value after each insert. You cannot assume that no other row was inserted (or insert attempted resulting in error) nor assume that the increment is one nor assume that a gap was not generated for some reason.