IDENTITY_INSERT On/Off in sql compact

Hi,

Im working in a project that uses sql server compact. While migrating some data from another database I needed to "Turn off" the identity insert :

SET IDENTITY_INSERT <tablename> OFF

and set it back to "ON" after the migration was completed. Unfortunately SQL Server Compact is giving me the following error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 5,Token in error = @.@.IDENTITY_INSERT ]

Im i doing something wrong or this instruction is not supported by the Compact edition.

Does anybody knows another way to do this?

Thank you

Alexander75

The IDENTITY_INSERT concept is not supported by SQL Compact Edition. Your best workaround for this is to use the ALTER TABLE / ALTER COLUMN IDENTITY to set the appropriate identity value for each row you want to insert. Not nice? You bet! But it's the only documented solution. I have it working on my database tranfer products so you can trust me it works.|||ok. Thanks|||You wouldn't happen to have a code sample for this, would you?

Thanks in advance...|||My samples use native code, but you can use the .NET CF classes for this. The critical part is to determine the IDENTITY properties before altering the table and this can be done by querying the INFORMATION_SCHEMA (see the BOL).