SET IDENTITY_INSERT (Transact-SQL)

Berlaku untuk:SQL ServerAzure SQL Database Azure SQL Managed Instance Azure Synapse AnalyticsSQL database di Microsoft Fabric

Dengan menggunakan pernyataan ini, Anda dapat menyisipkan nilai eksplisit ke dalam IDENTITY kolom tabel.

Artikel ini dan sintaksnya IDENTITY berbeda pada platform yang berbeda dari SQL Database Engine. Untuk Microsoft Fabric Data Warehouse, pilih Fabric Data Warehouse di daftar dropdown versi.

Konvensi sintaks transact-SQL

Sintaks

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

Argumen

database_name

Nama database tempat tabel yang ditentukan berada.

schema_name

Nama skema yang berisi tabel.

table_name

Nama tabel dengan kolom identitas.

Keterangan

Kapan saja, hanya satu tabel dalam sesi yang dapat mengatur properti IDENTITY_INSERT ke ON. Jika sebuah tabel sudah memiliki properti ini diatur ke ON, dan Anda mengeluarkan SET IDENTITY_INSERT ON pernyataan untuk tabel lain, SQL Server mengembalikan pesan kesalahan yang menyatakan SET IDENTITY_INSERT sudah ON, dan melaporkan tabel yang ON ditetapkan.

  • Ketika increment argumen IDENTITY fungsi positif, dan nilai yang dimasukkan lebih besar dari nilai identitas saat ini untuk tabel, SQL Database Engine secara otomatis menggunakan nilai baru yang dimasukkan sebagai nilai identitas saat ini.
  • Ketika increment argumen IDENTITY fungsi negatif, dan nilai yang dimasukkan lebih kecil dari nilai identitas saat ini untuk tabel, SQL Server secara otomatis menggunakan nilai baru yang dimasukkan sebagai nilai identitas saat ini.

Pengaturan SET IDENTITY_INSERT diatur pada waktu eksekusi atau run time dan bukan pada waktu penguraian.

Izin

Anda harus memiliki tabel tersebut atau memiliki ALTER izin di atas tabel tersebut.

Contoh

Contoh berikut membuat tabel dengan kolom identitas dan memperlihatkan bagaimana SET IDENTITY_INSERT pengaturan dapat digunakan untuk mengisi celah dalam nilai identitas yang DELETE disebabkan oleh pernyataan.

USE AdventureWorks2022;
GO

Buat tabel alat.

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

Sisipkan nilai ke dalam tabel produk.

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

Buat celah dalam nilai identitas.

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

SELECT *
FROM dbo.Tool;
GO

Cobalah untuk menyisipkan nilai ID eksplisit 3.

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

Kode sebelumnya INSERT mengembalikan kesalahan berikut:

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.

Atur IDENTITY_INSERT ke ON.

SET IDENTITY_INSERT dbo.Tool ON;
GO

Cobalah untuk menyisipkan nilai ID eksplisit 3.

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

SELECT *
FROM dbo.Tool;
GO

Letakkan tabel alat.

DROP TABLE dbo.Tool;
GO

Konten terkait

Berlaku untuk:Gudang di Microsoft Fabric

Gunakan SET IDENTITY_INSERT untuk menyisipkan nilai eksplisit ke kolom IDENTITY tabel di Fabric Data Warehouse. Gunakan SET IDENTITY_INSERT saat Anda perlu memasukkan nilai tertentu ke dalam kolom identitas, seperti saat migrasi data, pemulihan bencana, atau saat mengisi nilai sentinel dalam tabel dimensi.

Konvensi sintaks transact-SQL

Sintaks

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

Argumen

schema_name

Nama skema yang berisi tabel.

table_name

Nama tabel dengan kolom identitas.

Keterangan

Kapan saja, hanya satu tabel dalam sesi yang dapat mengatur properti IDENTITY_INSERT ke ON. Jika sebuah tabel sudah memiliki properti ini diatur ke dan ON Anda mengeluarkan SET IDENTITY_INSERT ON untuk tabel lain, akan terjadi kesalahan mengidentifikasi tabel yang properti tersebut sudah diatur.

Setelah menyelesaikan sisipan eksplisit, kembalikan IDENTITY_INSERT dan OFF jalankan DBCC CHECKIDENT dengan untuk RESEED menyelaraskan rentang identitas dan mencegah potensi konflik dengan nilai yang dihasilkan otomatis di masa depan.

Fabric Data Warehouse tidak menjamin keunikan nilai identitas saat IDENTITY_INSERT digunakan. Nilai yang disisipkan secara eksplisit dapat memperkenalkan duplikat kecuali Anda menjalankan DBCC CHECKIDENT untuk menyelaraskan metadata identitas sebelum sistem menghasilkan lebih banyak nilai.

Izin

Anda harus memiliki tabel tersebut atau memiliki ALTER izin di atas tabel tersebut.

Keterbatasan

SET IDENTITY_INSERT hanya berlaku untuk INSERT dan COPY INTO pernyataan. Aplikasi ini tidak memungkinkan Anda memperbarui nilai kolom identitas yang sudah ada.

Contoh

A. Sisipkan nilai sentinel ke dalam tabel dimensi

Penggunaan paling umum adalah IDENTITY_INSERT mengisi nilai sentinel, seperti -1 untuk "Tidak Dikenal," dalam tabel dimensi selama pengaturan atau migrasi data warehouse.

-- 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. Migrasi data sambil mempertahankan nilai identitas yang ada

Saat bermigrasi dari SQL Server atau Azure Synapse Analytics, gunakan IDENTITY_INSERT untuk mempertahankan nilai identitas yang ada dan menjaga integritas referensial.

-- 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);

C. Isi celah dalam nilai identitas

Jika baris dihapus dari tabel, gunakan IDENTITY_INSERT untuk mengisi celah dalam urutan identitas saat diperlukan.

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. Masukkan nilai eksplisit dengan COPY INTO

Pernyataan mendukung COPY INTOIDENTITY_INSERT opsi untuk menginget nilai eksplisit dalam perintah. COPY INTO opsi menimpa pengaturan tingkat sesi apa pun untuk 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'
);

Konten terkait

  • Last updated on