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

3 posts, 1 answers
  1. Jim
    Jim avatar
    7 posts
    Member since:
    Sep 2008

    Posted 11 Nov 2011 Link to this post


    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?
    - Jim
  2. Answer
    Zoran avatar
    534 posts

    Posted 16 Nov 2011 Link to this post

    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.

    the Telerik team

    NEW and UPDATED OpenAccess ORM Resources. Check them out!

  3. DevCraft banner
  4. Jim
    Jim avatar
    7 posts
    Member since:
    Sep 2008

    Posted 19 Nov 2011 Link to this post

    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
Back to Top