Add two rows with foreign key - SQL

Question

I'm worndering how I can add 2 rows, of which 1 depends on another, in 1 transaction.

INSERT INTO users (username) VALUES ('malcom.reynolds')

INSERT INTO spaceships (name, owner) 
VALUES ('Serenity', <<Malcom Reynold's row ID>>)

---

Reason I an doing is that the library I'm using does not return the rowid, and I need to commit the transaction as less as possible as I'm adding over a few million records!

Just for the record I'm using:

• SQL Server 2008
• Python
• pyodbc

---

Any idea? Would be really awesome :)

Answer 1

You can do this in one batch statement:

declare @key as int;
insert into users (username)
values ('malcom.reynolds');
set @key = (select scope_identity());
insert into spaceships (name, owner)
values ('Serenity', @key)