Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

SET IDENTITY_INSERT

Description

SET IDENTITY_INSERT toggles ability to insert user defined values into a field defined as an IDENTITY column.

Syntax

SET IDENTITY_INSERT <table> on | off;

Notes

Toggles IDENTITY insert values from and INSERT statement. If the value inserted is larger than the current identity value for the table, the new inserted value is used as the current identity value.

Only one table at a time within a session can be set to identity_insert on. An error is returned on the second attempt requiring the calling user to first disable support on the current table with SET IDENTITY_INSERT OFF.

Examples

CREATE TABLE salesdb (name CHAR(10), sales_id INTEGER IDENTITY (1,1);

SET IDENTITY_INSERT 'salesdb' ON;

INSERT VALUES ('joe', 199);

SET IDENTITY_INSERT 'salesdb' OFF:

INSERT VALUES ('sally');

Authorization

None. 

TOCIndex