Best way to get identity of inserted row in Linked server?
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.
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');