Лучший способ получить идентификатор вставленной строки на связанном сервере?

Я вставляю запись на удаленный сервер Sql, используя связанный сервер, теперь я хочу получить идентификатор вставленной записи. что-то вроде scope_identity() на локальном сервере. Мой удаленный сервер sql версии 2000.

Я видел этот пост, но не могу добавить какие-либо хранимые процедуры в удаленный sql-сервер


sql-server linked-server sql-server-2000
person Raymond Morphy    schedule 18.04.2011    source источник

Ответы (3)


arrow_upward
12
arrow_downward

Вы можете использовать удаленную сторону 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;

В качестве альтернативы вы можете использовать OPENQUERY:

SELECT *
FROM OPENQUERY(server, '
  INSERT INTO database.schema.table (columns) VALUES (values);
  SELECT SCOPE_IDENTITY() AS ID');
person Andriy M    schedule 19.04.2011
comment
Изменилось ли это поведение между SQL 2005/2008 и 2008R2? - person Gary Kindel; 15.03.2012
comment
@GaryKindel: Не уверен, что понимаю, что именно вы имеете в виду, но благодаря вашему комментарию я заметил, что тогда я пропустил одну вещь: N в начале строковой константы в первом фрагменте. (Сейчас обновлен мой ответ.) Это, безусловно, требуется в SQL Server 2008 R2, не уверен в более ранних версиях. Интересно, было ли отсутствие N причиной того, что вы задали вопрос. - person Andriy M; 15.03.2012
comment
Сегодня я столкнулся с проблемой, вызванной попыткой использовать Scope_indentity() для INSERT через связанный сервер. Это просто застало меня врасплох из-за этой ошибки в моем проекте. Мне просто интересно, всегда ли Scope_indentity() была локальной в SQL 2008. Думал, что это могло быть новым поведением с R2. - person Gary Kindel; 15.03.2012
comment
@GaryKindel: В 2008 R2 все так же, как и в 2008, ничего не изменилось, т.е. SCOPE_IDENTITY() является локальным для сервера (только что протестировано). Но почему, если вы используете SQL Server 2005+, вам лучше использовать предложение OUTPUT, например: INSERT INTO server.db.schema.table (columns) OUTPUT INSERTED.identity_column VALUES (values). Динамических частей тоже нет. - person Andriy M; 15.03.2012
comment
Единственный из них, который работал у меня, был самым первым, использующим мастер удаленного сервера..sp_executesql - person Adam; 26.02.2015

arrow_upward
2
arrow_downward

Еще один вариант, если у связанного пользователя есть разрешение на вызов процедур на связанном сервере:

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

Обновлено в соответствии с комментариями от 29 апреля 2019 г.

person Roman O    schedule 12.09.2016
comment
Мне пришлось использовать параметр OUTPUT следующим образом: DECLARE @ScopeIdentity int EXEC [linkedServerName].[database].[schema].sp_executesql N' INSERT INTO [table] ... SELECT SCOPE_IDENTITY()', N'@ScopeIdentityOUT INT OUTPUT', @ScopeIdentityOUT = @ScopeIdentity OUTPUT - person Honza Pačuta; 28.06.2017

arrow_upward
0
arrow_downward

попробуйте что-то вроде этого:

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

EXEC вернет набор результатов, содержащий значение SCOPE_IDENTITY()

если вам нужно сделать это для SQL Server 2005+, вы можете просто добавить OUTPUT INSERTED.IdentityColumn, чтобы получить результирующий набор идентификаторов. Добавьте INTO к этому OUTPUT, и вы сможете сохранить их в таблице/переменной таблицы на локальном компьютере.

person RacerX    schedule 18.04.2011
comment
Я пробовал, но возвращает NULL. что не так? Это мой код: exec('insert [(Worker)].Identification.dbo.test (name) values ​​(''gg''); select scope_identity()') - person Raymond Morphy; 19.04.2011
comment
Это не сработает. Он вернет NULL или 0. Это будет нормально работать для локальной базы данных SQL, но не для связанной. - person WiredEarp; 20.04.2016