CODEWITHSUNDEEP
sql-serversql-server-2000linked-server
Raymond Morphy
Raymond Morphy

Reputation: 2526

Fetch identity of inserted row in Linked Server?

I am inserting record in a remote SQL Server using Linked Server, Now I want to 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.

Upvotes: 15

Views: 20727

Answers (4)

Ulises
Ulises

Reputation: 11

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

Upvotes: 0

Andriy M
Andriy M

Reputation: 77717

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');

Upvotes: 15

Roman O
Roman O

Reputation: 3520

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.

Upvotes: 2

RacerX
RacerX

Reputation: 2784

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.

Upvotes: -1

Related Questions