how can I add records where I preset the desire key?

[Jim]

Hi,

I have code to copy database records from one database to another. The records have database maintained integer keys.
Even though I copy the ID field from source to destination record, the destination context.add funciton is replacing the key with a new higher valued key.

If I was doing this in dynamic SQL, against a SQL Server database, I could use ExecuteNonQuery("DBCC CHECKIDENT ... to set the next key,  however this is not supported in SQL Azure.

The next option is SET IDENTIY <tablename> ON, but then, how do I get OpenAccess to pass the key field on the insert statement?

I want to change this dynamically, since the on-going CRUD operations work as desired, and it is a special case, when I am refreshing or copying a database, that I want to keep the identity keys matching the source database.

Is there a way to do this in OpenAccess?
Thanks,
- Jim

[Zoran]

Hi Jim,

Yes this operation is possible if you follow the following stpes:

•  What you can do first, is to create your azure tables with no identity specification(I guess you have AUTOINC int identity at the moment). 
• Then you can perform the data migration which will result in OpenAccess transferring the objects from your existing database together with their key values.
• After the data migration is over, you can re-set the identity specification on your tables.

The above steps are the basic idea behind the implementation. If you share more details for your code that copies the data over, I will be able to assist you more regarding the concrete implementation.

Regards,
Zoran
the Telerik team

[Jim]

Thank you Zoran for your reply.  I do not understand how to turn off temporarily the primary keys in Azure, but I did find that I could use the execute non-query with parameters to do standard SLQ inserts.  Problem solved.
- Jim