Best way to get identity of inserted row in Linked server?

Question

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

Relief

Answer 1

You could use the remote side's sp_executesql:

DECLARE @ScopeIdentity TABLE (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');

Grouty

Did this behavior change between SQL 2005/2008 to 2008R2? – Cordoba

@GaryKindel: Not sure I understand what exactly you mean, but thanks to your comment I noticed that I missed one thing back then, the N at the beginning of the string constant in the first snippet. (Updated my answer now.) It is certainly required in SQL Server 2008 R2, not sure about earlier versions. I wonder if the absence of N was the reason behind your asking the question. – Grouty

I ran into a problem today caused by trying to use Scope_indentity() on a INSERT via a linkedserver. It just caught me by off guard by this error in my project. I was just wondering if Scope_indentity() was always local in SQL 2008. Thought this might have been new behavior with R2. – Cordoba

@GaryKindel: In 2008 R2 it's the same as in 2008, nothing's changed, i.e. SCOPE_IDENTITY() is local to the server (just tested). But why, if you are on SQL Server 2005+, you should be better off using the OUTPUT clause, like this: INSERT INTO server.db.schema.table (columns) OUTPUT INSERTED.identity_column VALUES (values). No dynamic parts, too. – Grouty

The only one of these that worked for me was the very first using the remote server's master..sp_executesql – Rutile

Answer 2

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

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

Updated per comments on 2019-04-29.

Eme

I had to use an OUTPUT parameter like this: DECLARE @ScopeIdentity int EXEC [linkedServerName].[database].[schema].sp_executesql N' INSERT INTO [table] ... SELECT SCOPE_IDENTITY()', N'@ScopeIdentityOUT INT OUTPUT', @ScopeIdentityOUT = @ScopeIdentity OUTPUT – Jonson

Answer 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.

Strontia

I tried it, but it returns NULL. what's wrong? This is my code: exec('insert [(Worker)].Identification.dbo.test (name) values (''gg'');select scope_identity()') – Relief

That wont work. It will return NULL or 0. It will work fine for a local SQL DB, but not for a linked one. – Rounds

Answer 4

thanks for guiding us

reviewing the documentation.

I found the solution.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15#syntax

DECLARE @IntVariable INT,@guardar int  
DECLARE @SQLString NVARCHAR(500);  
DECLARE @ParmDefinition NVARCHAR(500);  
DECLARE @ScopeIdentity TABLE (ID int);

/* Build the SQL string one time.*/  
SET @SQLString =  
N'INSERT INTO [DATABSE].DBO.TABLE (ITEM1,ITEM2) VALUES(getdate(),@ID) 
SELECT SCOPE_IDENTITY()';
SET @ParmDefinition = N'@ID tinyint';

/* Execute the string with the first parameter value. */  
SET @IntVariable = 1; 

INSERT INTO @ScopeIdentity
EXECUTE [SERVER_LINKED].master..sp_executesql @SQLString, @ParmDefinition, 
@ID = @IntVariable;  
    
SET @RETURN = (SELECT ID FROM @ScopeIdentity);
print @RETURN

Waki