IDENTITY_INSERT property set to OFF
With the IDENTITY INSERT property, SQL
Server allows you to permit or disallow explicit value insertion into identity
columns. When this attribute for a table is set to ON, it is advantageous in
some situations to be able to insert explicit values into the identity column.
But, if you try to add data to an identity column without first turning on the
IDENTITY INSERT property, an error will occur.
When the IDENTITY INSERT attribute is
set to OFF, explicit values cannot be inserted into the identity column. When
this option is selected, which the default is setting for most tables, SQL
Server generates the values in the identity column automatically.
To set the IDENTITY_INSERT property to
ON for a specific table, you can use the following syntax:
SET IDENTITY_INSERT table_name ON
Replace table_name with the name of
the table you want to modify. Once you have set the property to ON, you can
insert explicit values into the identity column. When you are done inserting
values, you should set the property back to OFF using the following syntax:
SET IDENTITY_INSERT
table_name OFF
Note that you can only set the
IDENTITY_INSERT property to ON for one table at a time. If you want to insert
explicit values into multiple tables with identity columns, you will need to
set the property to ON and OFF for each table separately.
Try this Way:
SET IDENTITY_INSERT table_name ON
/*
GO */
...insert command...
SET IDENTITY_INSERT table_name OFF
GO
0 Comments