1. IDENTITY_INSERT
Setting the auto-increment /identity field in sqlce for version 3.5 is simple. Just use the statement:
SET IDENTITY_INSERT MY_Table ON
After setting Identity Insert on, we can update the id field or set it manually on an insert. Be sure to turn it off when complete.
SET IDENTITY_INSERT MY_Table OFF
NOTE: this does not work with SQLCE 3.1 (see point 3 for work around)
2. Setting the next autoincrement Value
The following statement can be useful to set the next automatically generated autoincrement/Identity value
alter table MY_Table alter column Id IDENTITY(NEW_VALUE,NEW_STEP);
New Step is usually 1.
3. Work around for IDENTITY_INSERT on SQL compact edition 3.1 (assembly version 9.0.242.0)
ON SQLCE 3.1 IDENTITY_INSERT is not supported, to work around this, we need to set the autoincrement as in step 2, insert the row, and then reset the value. (typically we reset the value to max Id +1 ) something like:
NEW_VAL= Select Max(Id) from My_Table;
alter table My_table alter column Id IDENTITY(NEW_VAL,1);