Returning identity value when inserting new data

[osani]

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?

Thanks.

 

[jmcilhinney]

@@IDENTITY is a global variable that conatins the value of the last identity value generated. You can use "SELECT @@IDENTITY AS LastIdentity" to retrieve it. Note that it only contains the last value, so if your INSERT statement inserts multiple records you will only be able to get the ID for the last one. Go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp for more information.

 

[ManicCW]

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

 

[TechGnome]

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.

Tg

 

[jmcilhinney]

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.