Можно ли использовать IDENTITY_INSERT для таблицы, на которую ссылается СИНОНИМ?

На чтение 3 мин Опубликовано 16.09.2024

Я не могу найти ничего, что говорит о том, что я не могу это делать, но если я использую INSERT_IDENTITY на таблице, на которую ссылается синоним, я получаю ошибку:

‘MyOtherDB.MyTable’ не является пользовательской таблицей. Невозможно выполнить установку.

Ошибка возникает на операторе INSERT_IDENTITY (как ON, так и OFF)

Синоним был создан следующим образом (и схема для MyOtherDB до этого):

CREATE SYNONYM MyOtherDB.MyTable
FOR MyOtherDB.dbo.MyTable

Это работает без проблем:

SET IDENTITY_INSERT MyOtherDB.dbo.MyTable ON

и последующий INSERT, даже используя SYNONYM вместо трёхчастного названия, работает без проблем.

Но это не срабатывает

SET IDENTITY_INSERT MyOtherDB.MyTable ON

Я подозреваю, что это не разрешено, но хотел бы знать это точно, прежде чем сдаваться.

Мы копируем базу данных из PRODUCTION в DEV, и поскольку физические имена баз данных разные, мы просто настраиваем синонимы, и всё работает нормально на сервере DEV, так что это будет неудобно, если IDENTITY ON/OFF придется использовать жестко закодированные трёхчастные названия. Возможно, я смогу обойти это с помощью динамического SQL, но это тоже будет несколько неудобно.

Чтобы прояснить: подлежащая таблица определенно является физической таблицей, а не представлением и т.д.

Содержание

Ответ
Возможные решения:
Заключение:

Ответ

Вопрос о том, можно ли использовать IDENTITY_INSERT на таблице, на которую ссылается синоним, интересен и требует рассмотрения.

К сожалению, использование SET IDENTITY_INSERT с синонимами в SQL Server не поддерживается. Это видно из сообщения об ошибке, которое вы получили: 'MyOtherDB.MyTable' is not a user table. Cannot perform SET operation. Ошибка возникает потому, что SQL Server ожидает, что команда SET IDENTITY_INSERT будет применена непосредственно к пользовательской таблице, а не синониму.

Ваши наблюдения по поводу того, что команды SET IDENTITY_INSERT и последующий INSERT работают, когда вы ссылаетесь на фактическое имя таблицы (т.е. MyOtherDB.dbo.MyTable), подтверждают, что синонимы не могут быть использованы в этом контексте.

Возможные решения:

Использовать полное имя таблицы:
Вы можете продолжать использовать полное имя таблицы для установки IDENTITY_INSERT. Это, хотя и менее удобно, является простым и прямым решением.
```
SET IDENTITY_INSERT MyOtherDB.dbo.MyTable ON;
-- ваш INSERT
SET IDENTITY_INSERT MyOtherDB.dbo.MyTable OFF;
```
Динамический SQL:
Если вы действительно хотите использовать синонимы, вы можете использовать динамический SQL для выполнения команд. Например:
```
DECLARE @sql NVARCHAR(MAX);

SET @sql = 'SET IDENTITY_INSERT MyOtherDB.dbo.MyTable ON;';
EXEC sp_executesql @sql;

-- ваш INSERT с использованием синонима
INSERT INTO MyOtherDB.MyTable (ID, ColumnName) VALUES (1, 'Value');

SET @sql = 'SET IDENTITY_INSERT MyOtherDB.dbo.MyTable OFF;';
EXEC sp_executesql @sql;
```
Этот метод позволяет вам использовать синонимы, но при этом требует немного больше усилий.
Добавление дополнительной функции:
Если использование IDENTITY_INSERT и синонимов часто необходимо, возможно, имеет смысл создать хранимую процедуру, которая инкапсулирует логику управления идентичностью и адресацию к таблице.

Заключение:

Выводя это все вместе, вы не можете использовать SET IDENTITY_INSERT непосредственно на синониме. Лучший способ — использовать полное имя таблицы или реализовать динамический SQL для управления этим аспектом. Надеюсь, это поможет вам решить вашу проблему.