Add two rows with foreign key - SQL

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开发者_StackOverflow社区 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 :)

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)

Look at http://msdn.microsoft.com/en-us/library/aa259185%28SQL.80%29.aspx

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

INSERT INTO spaceships (name, owner) 
VALUES ('Serenity', SCOPE_IDENTITY() )

You can use the T-SQL scope_identity to return the new ID from the first INSERT statement.

Before your first INSERT, declare a variable to hold the value of the new ID:

DECLARE @ReturnValue int;

Right after your first INSERT, add this to set the value of that variable:

SELECT @ReturnValue = scope_identity()

Then, you can use the @ReturnValue variable in your second INSERT in place of :

INSERT INTO spaceships (name, owner) 
VALUES ('Serenity', @ReturnValue)

Or you can use OUTPUT. This is hadnier when you need to return more than one field or are doing a multitrow insert to begin with. You can use a table variable if you are doing multiple rows instead of an int variable.

DECLARE @key int
insert into users (username)
output inserted.userid 
values ('malcom.reynolds'); 

insert into spaceships (name, owner) 
values ('Serenity', @key)