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

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

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

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

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

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

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

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

  • Свойство 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.

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

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

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

Примечание! В качестве примера у меня будет выступать СУБД Microsoft SQL Server 2012 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');
   INSERT INTO TestTable (TextData) VALUES ('Строка 2');
   INSERT INTO TestTable (TextData) VALUES ('Строка 3');
   INSERT INTO TestTable (TextData) VALUES ('Строка 4');
   INSERT INTO TestTable (TextData) VALUES ('Строка 5');
   GO

   SELECT * FROM TestTable

Скриншот 1

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

 
   DELETE TestTable
   WHERE TextData = 'Строка 3';
   GO

Скриншот 2

Восстановление строк таблицы с автоинкрементным полем

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

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

Скриншот 3

Как видим, у нас не получилось вставить такую строку.

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

 
   SET IDENTITY_INSERT TestTable ON;
   
   INSERT INTO TestTable (ID, TextData) VALUES (3, 'Строка 3');
   
   SELECT * FROM TestTable
   GO

Скриншот 4

На этот раз все прошло как надо.

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

Заметка! Начинающим программистам рекомендую почитать мою книгу «Путь программиста T-SQL», в ней я подробно, с большим количеством примеров, рассказываю про другие полезные возможности языка Transact-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: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.