SET IDENTITY INSERT (SQL Server Compact)

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

Syntax

SET IDENTITY_INSERT table { ON | OFF } 

Arguments

  • table
    The name of a table with an identity column.

Remarks

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

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