INFO: Retrieving IDENTITY Value for Referential Integrity (134660)

SUMMARY

The Microsoft SQL Server version 6.0 documentation says that you can use the @@IDENTITY variable to retrieve the last-inserted identity value. The documentation, however, does not completely state the scope of the @@IDENTITY variable.

This article provides some additional information to clarify the scope of the @@IDENTITY variable.

MORE INFORMATION

The @@IDENTITY variable returns the value of the last inserted identity value.
The variable is unique to each connection.
The @@IDENTITY variable is updated each time you perform and insert into a table containing an identity column. This will include inserts that take place in an INSERT trigger. This means you are able to capture the value of the @@IDENTITY variable immediately following the insert operation and use it for foreign key references.
The @@IDENTITY variable is limited in scope to the local server and cannot be retrieved for remote or linked servers. Any global variable, in this case @@Identity, only has meaning within the context of the server on which it is executed. There is no direct way to obtain the value of a global variable from a different server than the one you are logged into.

The indirect way to obtain the value on a different server is to execute a stored procedure on that remote or linked server and have that stored procedure, which is executing in the context of the remote or linked server, gather the value and return it to the calling connection on the local server. Another option is to have your application make the connection directly to the remote or linked server instead of executing the query from within the local server. For the specific issue of adding records to child tables, you can add triggers to perform that step.

Also, inserts taking place on other connections are assigned the proper IDENTITY values, but your copy of the @@IDENTITY variable reflects only your last inserted IDENTITY value.