SET IDENTITY_INSERT

SET IDENTITY_INSERT#

Allows an explicit value to be inserted into a table’s identity column, instead of the data source generating one.

Syntax#

SET IDENTITY_INSERT table { ON | OFF }

Arguments#

table: A table with an identity column, whose backing provider accepts explicit identity values — the SQL Server family, PostgreSQL, Oracle, DB2, SAP HANA and Teradata. Other providers (for example Vertica, MySQL and StarRocks) reject SET IDENTITY_INSERT with an error stating the backing provider does not support inserting explicit values into an identity column.

Remarks#

By default (OFF), an INSERT that names the identity column in its column list is rejected:

Cannot insert explicit value for identity column in table '<table>' when IDENTITY_INSERT is set to OFF.

SET IDENTITY_INSERT table ON lifts that restriction for table, so a subsequent INSERT naming the identity column succeeds with the supplied value instead of a generated one. Only one table can have IDENTITY_INSERT set ON in a session; enabling it for a second table while another is still enabled is rejected, matching SQL Server. SET IDENTITY_INSERT table OFF re-enables the default rejection for that table, and the setting also resets automatically at the end of the session.

SET IDENTITY_INSERT is supported for identity tables whose backing provider accepts explicit identity values — the SQL Server family, PostgreSQL, Oracle, DB2, SAP HANA and Teradata; it is Querona’s own gate on top of the pushed-down INSERT, so the column must be an identity column (see CREATE TABLE). Other providers (for example Vertica, MySQL and StarRocks) reject the statement with an error stating the backing provider does not support inserting explicit values into an identity column.

On SQL Server sources, if session-settings synchronization is disabled for the data source, SET IDENTITY_INSERT fails rather than silently misbehaving. SET IDENTITY_INSERT table OFF for a table that is not the currently enabled one is a no-op, not an error — matching SQL Server. For the non-SQL-Server providers above, the setting opens only Querona’s own gate: those sources accept explicit identity values natively (their identity columns are GENERATED BY DEFAULT), so nothing is replayed to the source. Bulk loads (for example a client bulk copy) that supply values for the identity column are gated the same way as INSERT — they require SET IDENTITY_INSERT ON.

Permissions#

Requires ALTER permission on table.

Examples#

CREATE TABLE dbo.Product (
    Id   int          IDENTITY PRIMARY KEY,
    Name nvarchar(50) NOT NULL
);

INSERT INTO dbo.Product (Id, Name) VALUES (42, 'Widget');   -- error: IDENTITY_INSERT is OFF

SET IDENTITY_INSERT dbo.Product ON;
INSERT INTO dbo.Product (Id, Name) VALUES (42, 'Widget');   -- OK
SET IDENTITY_INSERT dbo.Product OFF;
INSERT INTO dbo.Product (Id, Name) VALUES (43, 'Gadget');   -- error again: IDENTITY_INSERT is OFF