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

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!

Solution

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)