How can I reliably store an ID so that I can use it in another INSERT statement?

Question

I have a Python program that executes a series of SQL statements via a loop. It first inserts data in one table, then it inserts data in another table.

This table doesn't use an identity seed, but it does use a sequence value for the IDs so to get the next available number of the sequence, I do this NEXT VALUE FOR transcript.nextid

The second table needs the ID from the first table and get that by doing this SELECT @ID = max(id) from transcript.transcript;.

Here is one instance in that loop:

DECLARE @ID BigInt

--start loop 

INSERT INTO transcript.transcript (id, title, departmentId)
VALUES (NEXT VALUE FOR transcript.nextid, 'binarySequence', 754)

WAITFOR DELAY '00:00:01';

SELECT @ID = MAX(id) FROM transcript.transcript;

INSERT INTO transcript.externalSources (transcriptId, governanceId, hint)
VALUES (@ID, 00993846122, 'binarySequence');

The problem I am running into is that sometimes the value I get when doing this:

NEXT VALUE FOR transcript.nextid

does not always equal the value I get with this

SELECT @ID = MAX(id) from transcript.transcript;

Is there a way to reliably get the ID value?

Thanks!

Answer 1

You can use the OUTPUT clause so that you can access the inserted values directly within the statement, and insert them directly into the other table. This prevents you from getting anyone else's MAX(), or using serializable, e.g.:

INSERT transcript.transcript(id, title, departmentId)
  OUTPUT inserted.id, 00993846122, inserted.title
  INTO transcript.externalSources(transcriptId, governanceId, hint)
SELECT NEXT VALUE FOR dbo.nextid, 'binarySequence', 754;

• Example db<>fiddle (but limited to dbo schema, sorry)

Answer 2

When using an identity column, you can use scope_identity().

But since you're using a sequence, one option is using NEXT VALUE FOR to set the variable first, then use this one variable for both inserts:

DECLARE @ID BigInt

--start loop 
SET @ID = NEXT VALUE FOR transcript.nextid;

INSERT INTO transcript.transcript (id, title, departmentId)
VALUES (@ID, 'binarySequence', 754)

WAITFOR DELAY '00:00:01';

INSERT INTO transcript.externalSources (transcriptId, governanceId, hint)
VALUES (@ID, 00993846122, 'binarySequence');

This is especially useful if there are more than two tables (such that OUTPUT/INTO might not be adequate). The issue with this option is we're now crossing several statements that change different tables, so you should also think about using a transaction to ensure things can roll back cleanly in the event everything doesn't go as expected.