Best way to get identity of inserted row in Linked

问题:

I am inserting record in a remote Sql Server using Linked server, Now I wanna get the id of inserted record. something like scope_identity() in local server. My remote sql server is 2000 version.

I have seen this post but I can't add any stored procedures in remote sql server

回答1:

You could use the remote side's sp_executesql:

DECLARE @ScopeIdentity (ID int);
INSERT INTO @ScopeIdentity
EXEC server.master..sp_executesql N'
  INSERT INTO database.schema.table (columns) VALUES (values);
  SELECT SCOPE_IDENTITY()';
SELECT * FROM @ScopeIdentity;

---

Alternatively, you could use OPENQUERY:

SELECT *
FROM OPENQUERY(server, '
  INSERT INTO database.schema.table (columns) VALUES (values);
  SELECT SCOPE_IDENTITY() AS ID');

回答2:

Yet another variation, in case linked user has permission to call procedures on linked server:

DECLARE @ScopeIdentity int
EXEC @ScopeIdentity = [linkedServerName].[database].[schema].sp_executesql N'
  INSERT INTO [table] ...
  SELECT SCOPE_IDENTITY()'

回答3:

try something like this:

--when RemoteTable is (Rowid int identity(1,1) primary key, rowValue varchar(10))
exec ('INSERT server.database.owner.RemoteTable (rowValue) VALUES (''wow'');select SCOPE_IDENTITY()')

the EXEC will return a result set containing the SCOPE_IDENTITY() value

if you have to do this for SQL Server 2005+ you can just add an OUTPUT INSERTED.IdentityColumn to get a result set of the identitie(s). Add an INTO onto that OUTPUT and you can store them in a table/table variable on the local machine.