sql-server - 使用 SQL Server 从跨服务器插入中获取最后一个 ID

Question

我正在运行一个跨服务器插入

INSERT INTO server.database.dbo.table (Field) VALUES('test')

之后，我需要获取最后插入的 ID。但是，当我运行 scope_identity 函数时，我没有从外部服务器获取最新的 ID。

SELECT @ID=SCOPE_IDENTITY()

我如何从跨服务器插入中检索最后一个 ID？

Answer 1

来自 http://msdn.microsoft.com/en-us/library/ms187342.aspx

The scope of the @@IDENTITY function is current session on the local server on which it is executed. This function cannot be applied to remote or linked servers. To obtain an identity value on a different server, 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 identity value and return it to the calling connection on the local server.