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

2021-09-10 00:00:00 tsql sql-server

我正在运行跨服务器插入

I am running a cross server insert

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

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

Afterward, I need to get the id of the last insert. However, when I run the the scope_identity function, I don't get the latest id from the foreign server.

SELECT @ID=SCOPE_IDENTITY()

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

How would I retrieve the last id from a cross server insert?

推荐答案

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

@@IDENTITY 函数的作用域是本地服务器上的当前会话在其上执行.这个功能不能应用于远程或链接服务器.获取身份值在不同的服务器上，执行该远程存储过程或链接服务器并存储过程(在远程或链接的上下文服务器)收集身份值和将其返回到调用连接本地服务器.

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.

相关文章