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

Вопросы и ответы

Я не могу найти ничего, что говорит о том, что я не могу это делать, но если я использую 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), подтверждают, что синонимы не могут быть использованы в этом контексте.

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

  1. Использовать полное имя таблицы:
    Вы можете продолжать использовать полное имя таблицы для установки IDENTITY_INSERT. Это, хотя и менее удобно, является простым и прямым решением.

    SET IDENTITY_INSERT MyOtherDB.dbo.MyTable ON;
    -- ваш INSERT
    SET IDENTITY_INSERT MyOtherDB.dbo.MyTable OFF;
  2. Динамический 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;

    Этот метод позволяет вам использовать синонимы, но при этом требует немного больше усилий.

  3. Добавление дополнительной функции:
    Если использование IDENTITY_INSERT и синонимов часто необходимо, возможно, имеет смысл создать хранимую процедуру, которая инкапсулирует логику управления идентичностью и адресацию к таблице.

Заключение:

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

Оцените материал
Добавить комментарий

Капча загружается...