Export (0) Print
Expand All
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 3.5 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 3.5 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 >)

Bb734402.note(en-us,SQL.100).gifNote:
If < NewSeed > is outside the allocated range, Merge Replication could break.

Show:
© 2014 Microsoft