SET IDENTITY_INSERT (Transact-SQL)

Область применения:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureAzure Synapse AnalyticsБаза данных SQL в Microsoft Fabric

Используя это утверждение, вы можете вставлять явные значения в IDENTITY столбец таблицы.

Эта статья и синтаксис IDENTITY различаются на разных платформах SQL ядро СУБД. Для Microsoft Fabric Data Warehouse выберите Fabric Data Warehouse в выпадающем списке версий.

Соглашения о синтаксисе Transact-SQL

Синтаксис

SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { ON | OFF }

Аргументы

database_name

Название базы данных, в которой находится указанная таблица.

schema_name

Название схемы, содержащей таблицу.

table_name

Имя таблицы с столбцом удостоверений.

Замечания

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

  • Когда increment аргумент IDENTITY функции положительный, а вставленное значение больше текущего значения идентичности таблицы, SQL ядро СУБД автоматически использует новое вставленное значение в качестве текущего значения идентичности.
  • Когда increment аргумент IDENTITY функции отрицателен, а вставленное значение меньше текущего идентификатора таблицы, SQL Server автоматически использует новое вставленное значение в качестве текущего тождественного значения.

Параметр SET IDENTITY_INSERT устанавливается во время выполнения или выполнения, а не во время синтаксического анализа.

Разрешения

Вы должны владеть столом или иметь ALTER разрешение на стол.

Примеры

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

USE AdventureWorks2022;
GO

Создайте таблицу инструментов.

CREATE TABLE dbo.Tool
(
    ID INT IDENTITY NOT NULL PRIMARY KEY,
    Name VARCHAR (40) NOT NULL
);
GO

Вставка значений в таблицу продуктов.

INSERT INTO dbo.Tool (Name)
VALUES ('Screwdriver'),
    ('Hammer'),
    ('Saw'),
    ('Shovel');
GO

Создайте пробел в значениях удостоверений.

DELETE dbo.Tool
WHERE Name = 'Saw';
GO

SELECT *
FROM dbo.Tool;
GO

Попробуйте вставить явное значение идентификатора 3.

INSERT INTO dbo.Tool (ID, Name)
VALUES (3, 'Garden shovel');
GO

Предыдущий INSERT код возвращает следующую ошибку:

An explicit value for the identity column in table 'AdventureWorks2022.dbo.Tool' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Задайте для IDENTITY_INSERT значение ON.

SET IDENTITY_INSERT dbo.Tool ON;
GO

Попробуйте вставить явное значение идентификатора 3.

INSERT INTO dbo.Tool (ID, Name)
VALUES (3, 'Garden shovel');
GO

SELECT *
FROM dbo.Tool;
GO

Удалить таблицу инструментов.

DROP TABLE dbo.Tool;
GO

Связанное содержимое

Область применения:хранилище в Microsoft Fabric

Используйте SET IDENTITY_INSERT для вставки явных значений в IDENTITY столбец таблицы в Fabric Data Warehouse. Используйте SET IDENTITY_INSERT их при вставке определённых значений в столбец идентичности, например, при миграции данных, восстановлении после катастрофы или при заполнении значений Sentinel в таблицах измерений.

Соглашения о синтаксисе Transact-SQL

Синтаксис

SET IDENTITY_INSERT [ schema_name. ] table_name { ON | OFF }

Аргументы

schema_name

Название схемы, содержащей таблицу.

table_name

Имя таблицы с столбцом удостоверений.

Замечания

В любое время только одна таблица в сеансе может иметь свойство IDENTITY_INSERT для ON. Если в таблице уже установлено это свойство и ON вы выдаёте SET IDENTITY_INSERT ON для другой таблицы, ошибка определяет таблицу, для которой это свойство уже установлено.

После выполнения явных вставок установите IDENTITY_INSERT обратно и OFF запустите DBCC CHECKIDENT , RESEED чтобы перенастроить диапазон идентичности и предотвратить возможные конфликты с будущими автоматически сгенерируемыми значениями.

Fabric Data Warehouse не гарантирует уникальность тождественных значений при IDENTITY_INSERT использовании. Явно вставленные значения могут создавать дубликаторы, если только вы не запустите DBCC CHECKIDENT для перенастройки метаданных идентичности до того, как система сгенерирует новые значения.

Разрешения

Вы должны владеть столом или иметь ALTER разрешение на стол.

Limitations

SET IDENTITY_INSERT применяется только к INSERT и COPY INTO заявлениям. Он не позволяет обновлять существующие значения столбцов идентичности.

Примеры

А. Вставьте значения стражей в таблицу размерностей

Самое распространённое применение IDENTITY_INSERT — заполнение значений сентинелов, таких -1 как «Неизвестно», в таблицах размерностей при настройке или миграции хранилища данных.

-- Create a dimension table with an IDENTITY column
CREATE TABLE dbo.DimCustomer (
    CustomerKey BIGINT IDENTITY,
    CustomerName VARCHAR(100),
    Email VARCHAR(200)
);

-- Enable IDENTITY_INSERT to add sentinel rows
SET IDENTITY_INSERT dbo.DimCustomer ON;

INSERT INTO dbo.DimCustomer (CustomerKey, CustomerName, Email)
VALUES (-1, 'Unknown', 'N/A');

INSERT INTO dbo.DimCustomer (CustomerKey, CustomerName, Email)
VALUES (-2, 'Not Applicable', 'N/A');

SET IDENTITY_INSERT dbo.DimCustomer OFF;

-- Reseed to prevent conflicts with future auto-generated values
DBCC CHECKIDENT('dbo.DimCustomer', RESEED);

B. Мигрировать данные при сохранении существующих тождественных значений

При миграции с SQL Server или Azure Synapse Analytics используйте IDENTITY_INSERT их для сохранения существующих значений идентичности и сохранения целостности ссылок.

-- Assume dbo.DimProduct has an IDENTITY column named ProductKey
SET IDENTITY_INSERT dbo.DimProduct ON;

INSERT INTO dbo.DimProduct (ProductKey, ProductName, Category, ListPrice)
VALUES (1, 'Widget A', 'Hardware', 19.99),
       (2, 'Widget B', 'Hardware', 29.99),
       (3, 'Gadget C', 'Electronics', 49.99);

SET IDENTITY_INSERT dbo.DimProduct OFF;

-- Reseed after migration
DBCC CHECKIDENT('dbo.DimProduct', RESEED);

С. Заполнить пробел в ценностях идентичности

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

CREATE TABLE dbo.Tool (
    ID BIGINT IDENTITY,
    Name VARCHAR(40) NOT NULL
);

INSERT INTO dbo.Tool (Name)
VALUES ('Screwdriver'), ('Hammer'), ('Saw'), ('Shovel');

-- Delete a row, creating a gap
DELETE FROM dbo.Tool WHERE Name = 'Saw';

-- Fill the gap with an explicit value
SET IDENTITY_INSERT dbo.Tool ON;

INSERT INTO dbo.Tool (ID, Name)
VALUES (3, 'Garden shovel');

SET IDENTITY_INSERT dbo.Tool OFF;
DBCC CHECKIDENT('dbo.Tool', RESEED);

D. Вставьте явные значения с помощью COPY INTO

Оператор COPY INTO поддерживает IDENTITY_INSERT возможность вводить явные значения внутри команды. COPY INTO Опции переопределяют любые настройки сессионного уровня для IDENTITY_INSERT.

COPY INTO dbo.Employees (EmployeeID 1, FirstName 2, LastName 3)
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV',
    IDENTITY_INSERT = 'ON'
);

Связанное содержимое

  • Last updated on