Export (0) Print
Expand All
Expand Minimize
3 out of 6 rated this helpful - Rate this topic

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.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.