INFO: Retrieving IDENTITY Value for Referential Integrity

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.