TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

SET IDENTITY INSERT (SQL Server Compact)

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


SET IDENTITY_INSERT table { ON | OFF } 

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.

Show:
© 2016 Microsoft