Insert identity ms sql: SET IDENTITY_INSERT (Transact-SQL) — SQL Server

Вставка определенных значений в столбец идентификаторов с помощью SQL

Вставка определенных значений в столбец идентификаторов с помощью SQL

Артур Фуллер исследует значение столбцов идентификаторов и полезность их произвольного использования.
ценности и обсуждает способы использования SQL
Параметр IDENTITY_INSERT сервера 2000.

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

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

— Создать тестовую таблицу.
CREATE TABLE TestIdentityGaps
    (
        ID int IDENTITY
PRIMARY KEY,
        Описание
varchar(20)
    )
GO
— Вставить несколько значений. Слово INTO является необязательным:
INSERT [INTO] TestIdentityGaps (Description) VALUES («One»)
INSERT [INTO] TestIdentityGaps (Description) VALUES («Two»)
INSERT [INTO] TestIdentityGaps (Description) VALUES («Три») ‘)
INSERT [INTO] TestIdentityGaps (Description) VALUES («Четыре»)
INSERT [INTO] TestIdentityGaps (Description) VALUES («Пять»)
INSERT [INTO] TestIdentityGaps (Description) VALUES («Шесть»)
GO

Теперь удалите пару строк:

УДАЛИТЬ TestIdentityGaps
ГДЕ Описание IN(‘Два’, ‘Пять’)

Поскольку мы написали код, мы знаем, что значения
«Два» и «Пять» отсутствуют. Мы хотим вставить две строки, чтобы заполнить
этих пробелов. Два простых оператора INSERT не заполнят счет;
скорее, они создадут ПК в конце последовательности.

INSERT [INTO] TestIdentityGaps (Description) ЗНАЧЕНИЯ
(«Two Point One»)
INSERT [INTO] TestIdentityGaps (Description) VALUES («Five Point
One’)
GO
SELECT * FROM TestIdentityGaps

Вы также не можете явно установить значение столбца идентификаторов
:

— Попробуйте вставить явное значение идентификатора 2. Возвращает
предупреждение.
INSERT INTO TestIdentityGaps (id, Description) VALUES(2, ‘Two
Point One’)
GO

Чтобы обойти эту проблему, SQL
Server 2000 предоставляет параметр IDENTITY_INSERT. Чтобы принудительно вставить
строку с определенным значением, введите команду и
, затем следуют за ним ваши конкретные вставки:

SET IDENTITY_INSERT TestIdentityGaps ON
INSERT INTO TestIdentityGaps (id, Description) VALUES(2, ‘Two
Point One’)
INSERT INTO TestIdentityGaps (id, Description) VALUES(5, ‘Five
Point One’)
GO
ВЫБЕРИТЕ * ОТ TestIdentityGaps

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

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

Вы можете вставить определенные значения в таблицу
со столбцом Identity, но для этого вы должны сначала установить для
IDENTITY_INSERT значение ON. Если вы этого не сделаете, вы получите сообщение об ошибке
. Даже если вы установите для IDENTITY_INSERT значение ON, а затем
попытаетесь вставить существующее значение, вы получите сообщение об ошибке
.

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

артурфуллер

Опубликовано:
.

Изменено:

Увидеть больше Управление данными

Поделиться: вставка определенных значений в столбец идентификаторов с помощью SQL

  • Управление данными

Выбор редактора

  • Изображение: Rawpixel/Adobe Stock

    ТехРеспублика Премиум

    Редакционный календарь TechRepublic Premium: ИТ-политики, контрольные списки, наборы инструментов и исследования для загрузки

    Контент TechRepublic Premium поможет вам решить самые сложные проблемы с ИТ и дать толчок вашей карьере или следующему проекту.

    Персонал TechRepublic

    Опубликовано:

    Изменено:

    Читать далее

    Узнать больше

  • Изображение: ириска/Adobe Stock

    Искусственный интеллект

    Шпаргалка ChatGPT: полное руководство на 2023 год

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

    Меган Краус

    Опубликовано:

    Изменено:

    Читать далее

    Увидеть больше Искусственный интеллект

  • Изображение: Каролина Грабовска/Pexels

    Начисление заработной платы

    8 лучших программ для расчета заработной платы с открытым исходным кодом на 2023 год

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

    Девин Партида

    Опубликовано:

    Изменено:

    Читать далее

    Увидеть больше

  • Источник: ArtemisDiana/Adobe Stock

  • Изображение: Nuthawut/Adobe Stock

    Программное обеспечение

    10 лучших программ и инструментов для управления проектами на 2023 год

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

    Коллинз Аюя

    Опубликовано:

    Изменено:

    Читать далее

    Увидеть больше Программное обеспечение

  • Изображение Lede, созданное Марком Кэлином из общедоступных изображений.

Как исправить «Не удается вставить явное значение для столбца идентификации в таблице»

Автор: Мухаммед Уссама, 20 марта 2023 г.

3 минуты чтения

Столбец первичного ключа часто устанавливается на автоинкремент при построении базы данных SQL Server. Для этого для столбца первичного ключа установлено ограничение IDENTITY. Начальное местоположение и шаг приращения передаются в столбец IDENTITY в качестве параметров. Затем всякий раз, когда добавляется новая запись и для вставки идентификатора установлено значение OFF, значение столбца IDENTITY увеличивается на заранее определенный шаг, обычно число. Более того, свойство IDENTITY INSERT установлено в ON только для одной таблицы в одном сеансе.

В этой статье мы обсудим ошибку «Невозможно вставить явное значение для столбца идентификаторов в таблице <имя таблицы>, когда для параметра IDENTITY_INSERT установлено значение OFF» , как показано ниже.

Отключение «IDENTITY INSERT» и вставка данных без «PRIMARY KEY ID» в оператор вставки

Ошибка возникает, когда пользователь установил для «identity_insert» значение «OFF». Затем пытается явно вставить данные в столбец первичного ключа таблицы. Это можно пояснить на примере ниже.

Создание базы данных и таблицы:

Сначала создайте базу данных с именем «appuals».

Создание базы данных с именем appuals.

Создайте таблицу с именем «человек», используя следующий код. Построена таблица с использованием человека «PRIMARY KEY IDENTITY» 

 CREATE TABLE
  (
 ID INT IDENTITY (1, 1),
 первое_имя VARCHAR(MAX) ,
 last_name VARCHAR(MAX)
)

Создание таблицы с именем «person»

Синтаксис для установки «identity_insert off | на”:

«отключите identity_insert | on» поможет нам устранить эту ошибку. Правильный синтаксис для этого оператора показан ниже.

 SET IDENTITY_INSERT <имя базы данных> . <имя схемы>. <имя таблицы> { ВЫКЛ | ON }

При этом первый аргумент <имя базы данных> — это имя базы данных, в которой находится таблица. Второй аргумент <имя схемы> показывает схему, к которой принадлежит эта таблица, значение идентификатора которой должно быть установлено на ON или 9.0169 ВЫКЛ . Третий аргумент <имя таблицы> — это таблица со столбцом идентификаторов.

Существует два принципиально разных способа вставки данных в таблицу без ошибок. Они считаются решением этой ошибки и обсуждаются ниже.

Ошибка 1: установите identity_insert OFF

В первом случае мы будем вставлять данные в таблицу с параметром «IDENTITY INSERT» , установленным на «OFF» . Таким образом, если идентификатор присутствует в операторе INSERT, вы получите ошибку 9.0169 «Невозможно вставить явное значение для столбца идентификаторов в таблице «человек», если для параметра IDENTITY_INSERT установлено значение OFF».

Выполните следующий код на вкладке запроса.

 отключить личность identity_insert;
 вставить в человека(ID,имя,фамилия)
 значения (3, «Садия», «Маджид»),
 (4,'Мишель','Рональд')

Результат будет таким.

Ошибка возникает из-за отключения «IDENTITY INSERT OFF» и вставки данных с «PRIMARY KEY ID» в оператор вставки

Решение:

При отключении IDENTITY INSERT PRIMARY KEY ID НЕ ДОЛЖЕН ПРИСУТСТВОВАТЬ в операторе вставки

Теперь выполните следующий код на вкладке запроса

 set identity_insert person выключено;
 вставить в человека(имя,фамилия)
 значения('Садия','Маджид'),
 ("Мишель", "Рональд")

Отключение параметра «IDENTITY INSERT» и вставка данных без «PRIMARY KEY ID» в оператор вставки

Это позволит вставить данные в таблицу без ошибки. Кроме того, пользователю не требуется вводить идентификатор PRIMARY KEY ID, вместо этого он автоматически добавит уникальное значение идентификатора, как показано на рисунке ниже.

Ошибка 2: установите identity_insert ON

Во втором случае мы будем вставлять данные в таблицу с параметром «IDENTITY INSERT» , установленным на «ON» . Таким образом, если идентификатор отсутствует в операторе INSERT, вы получите сообщение об ошибке ”Сообщение 545, уровень 16, состояние 1, строка 17. Явное значение должно быть указано для столбца идентификаторов в таблице «человек» либо в том случае, если IDENTITY_INSERT имеет значение установлено значение ON или когда пользователь репликации вставляет данные в столбец идентификаторов NOT FOR REPLICATION».

Ошибка возникает из-за вставки данных в таблицу с параметром «IDENTITY INSERT», установленным на «ON». Таким образом, идентификатор PRIMARY KEY ID должен быть вставлен пользователем

.

Решение:

При включении «IDENTITY INSERT ON» «PRIMARY KEY   ID» должен присутствовать в операторе вставки.

Выполните следующий код на вкладке запроса

 установите лицо identity_insert на;
 вставить в человека(ID,имя,фамилия)
 значения(5,'Валет','черный'),
 (6, 'Джон', 'Вики')

Вставка данных в таблицу с параметром «IDENTITY INSERT», установленным на «ON», и с идентификатором первичного ключа в операторе вставки.

Это позволит вставить данные в таблицу без ошибки. Таким образом, PRIMARY KEY ID явно требуется для вставки пользователем. Кроме того, он не будет автоматически добавлять значение уникального идентификатора, как показано на рисунке ниже.

Если вы « SET IDENTITY INSERT ON» , он останется включенным на весь сеанс. Таким образом, вы можете добавить столько записей, сколько хотите, как только это будет установлено. Это также относится только к сеансу, в котором он включен.