Returning identity value when inserting new data


Apr 7, 2005
Programming Experience
Hi all,

My database is set up to assign a bigInt identity data type to the record when a new person is entered. The identity starts at 11000 and is incremented by 1.

I have 4 tables that are relationally joined so when a new record is entered into the main table a record with the same identity value needs to be entered into the other tables.

I want to assure that the same number is being entered into each table for the same person. Is there a way to return the identity value and then insert it into the other tables?

How to return ID value

After calling executenonquery on insert command use:

Dim selectSQL AsString = "SELECT @@Identity FROM Table"
Dim selectSQLCmd As SqlCommand = New SqlCommand(selectSQL, connObject)
Dim ID as Int = selectSQLCmd.ExecuteScalar
I'd like to follow that up with: And if someone ELSE manages to insert a record BEFORE you can SELECT @@Identity, you'll get theirs.

If it's all done inside a stored procedure it isn't such a big deal, but if you have to make a second trip back to the database, then it becomes a risk.

There is also SCOPE_IDENTITY and IDENT_CURRENT that have narrower scope than @@IDENTITY. If you are not using Access, I think you should also be able to use an output parameter and multiple SQL statements in the one command to get the ID for each row inserted.