Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize


Allows explicit values to be inserted into the identity column of a table.



The name of a table with an identity column.

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server Compact returns an error message. The message states that SET IDENTITY_INSERT is already ON and reports the table name. Using the SET IDENTITY_INSERT command without a table name is not permitted. If the Transact-SQL command is issued without the table name, an error is thrown.

When IDENTITY_INSERT is ON, SQL Server Compact does not automatically update IDENTITY related metadata like AUTOINC_NEXT when the newly provided IDENTITY value is more than or equal to AUTOINC_NEXT. This behavior is different from SQL Server. Although not as efficient as SET IDENTITY_INSERT, SQL Server auto-increment behavior can be mimicked using the following technique.

ALTER TABLE < TableName > ALTER COLUMN < ColumnName > < Datatype > IDENTITY(< NewSeed >, < NewStep >)

Note Note

If < NewSeed > is outside the allocated range, Merge Replication could break.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
© 2015 Microsoft