Свойство таблицы IDENTITY INSERT в Microsoft SQL Server

Всем привет! Сегодня я расскажу о таком, в некоторых случаях очень полезном, свойстве таблицы в Microsoft SQL Server как IDENTITY_INSERT. Используя именно это свойство мы можем вставить явное значение в автоинкрементный столбец с IDENTITY, которое ранее было удалено, т.е. заполнить или восстановить пропущенные значения идентификаторов.

Свойство таблицы IDENTITY INSERT в Microsoft SQL Server

Наверное, у многих программистов SQL Server возникала ситуация, когда в таблице, в которой определена спецификация идентификатора, по какой-то причине удаляются некоторые записи, а затем возникает необходимость восстановить эти записи, причем со значениями старых идентификаторов.

Первое, что приходит на ум – это конечно удалить идентификацию, вставить строки с необходимыми значениями, а затем восстановить идентификацию, но для этого, как Вы понимаете, необходимо выполнить достаточно много манипуляций, которые могут повлиять на ход текущей работы, поэтому их нужно делать быстро, да и лучше в тот момент, когда в базе данных нет работающих пользователей.

Однако, на самом деле есть более простой, а главное правильный способ, который позволяет вставлять значения в столбец идентификаторов таблицы. Он заключается в использовании свойства IDENTITY INSERT.

Свойство IDENTITY_INSERT в Microsoft SQL Server

IDENTITY_INSERT – это свойство таблицы, которое позволяет вставлять явные значения в столбец идентификаторов таблицы, т.е. в столбец с IDENTITY. Значение вставляемого идентификатора может быть как меньше текущего значения, так и больше, например, для того чтобы пропустить определенный интервал значений.

При работе с данным свойством необходимо учитывать некоторые нюансы, давайте их рассмотрим:

  • Свойство IDENTITY_INSERT может принимать значение ON только для одной таблицы в сеансе, т.е. одновременно для двух и более таблиц в сеансе нельзя выставить IDENTITY_INSERT в ON. Если необходимо в одной SQL инструкции использовать IDENTITY_INSERT ON для нескольких таблиц, нужно сначала выставить значение в OFF у таблицы, которая уже обработана, а затем установить IDENTITY_INSERT в ON для следующей таблицы;
  • Если значение идентификатора, которое вставляется, больше текущего значения, то SQL сервер автоматически будет использовать вставленное значение в качестве текущего, т.е. если, например, следующее значение идентификатора 5, а Вы, используя IDENTITY INSERT, вставляете идентификатор со значением 6, то автоматически следующим значением идентификатора станет значение 7;
  • Для того чтобы использовать IDENTITY_INSERT пользователь должен иметь соответствующие права, а именно быть владельцем объекта или входить в состав роли сервера sysadmin, роли базы данных db_owner или db_ddladmin.

Пример использования IDENTITY_INSERT на T-SQL

Итак, давайте рассмотрим ситуацию, когда у нас возникла необходимость вставить значение в столбец идентификаторов, допустим, строка с этим значением была удалена, а нам ее нужно восстановить.

Для начала давайте рассмотрим исходные данные.

Исходные данные

Для примера давайте создадим тестовую таблицу, в которой есть столбец идентификаторов с IDENTITY, и заполним ее какими-нибудь данными.

Примечание! В примере используется версия Microsoft SQL Server 2019 Express.

   
   CREATE TABLE TestTable(
     Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
     TextData VARCHAR(50) NOT NULL
   );

   GO

   INSERT INTO TestTable (TextData)
     VALUES ('Строка 1'),
            ('Строка 2'),
            ('Строка 3'),
            ('Строка 4'),
            ('Строка 5');

    GO

   SELECT * FROM TestTable;

Скриншот 1

Полезные материале по теме:

Удаление одной записи из таблицы

Теперь давайте удалим строку, в которой значение идентификатора равняется 3.

   
   DELETE TestTable
   WHERE Id = 3;

   SELECT * FROM TestTable;

Скриншот 2

Мы видим, что запись удалена.

Восстановление удаленной записи с определенным значением идентификатора

У нас в таблице отсутствует запись со значение Id = 3 и нам ее нужно восстановить именно с Id = 3, для этого мы, как начинающие программисты SQL, пробуем сначала так.

   
   INSERT INTO TestTable (Id, TextData)
     VALUES (3, 'Строка 3');

Скриншот 3

Как видим, у нас не получилось вставить такую строку, SQL Server вернул ошибку «Невозможно вставить явное значение для столбца идентификаторов в таблице».

Чтобы все-таки добавить запись с данным значением, давайте используем свойство IDENTITY_INSERT, т.е. выставим его значение для таблицы TestTable в ON.

   
   SET IDENTITY_INSERT TestTable ON;
   
   INSERT INTO TestTable (Id, TextData)
     VALUES (3, 'Строка 3');

   SELECT * FROM TestTable;

Скриншот 4

На этот раз все прошло успешно, и запись с восстановленным значением идентификатора, как видим, добавлена.

Примечание! Если столбец с идентификатором является первичным ключом, как в нашем примере, то при попытке вставить значение идентификатора, которое уже есть, сработает ограничение PRIMARY KEY, другими словами, уникальные ключи продолжают действовать.

Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.

На этом у меня все, надеюсь, материал был Вам полезен!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментарии: 2
  1. Аватар
    Павел

    А что если IDENTITY нужно не вставить, а изменить на другое значение? Это вообще реально?

    1. Админ
      Админ (автор)

      Стандартными способами это сделать не получится. При попытке выполнить запрос UPDATE на столбце со свойством IDENTITY получите ошибку «Cannot update identity column…».
      Но решение, конечно, найти можно.

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

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.