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

    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
  2. Answer
    Zoran
    Admin
    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.

    Regards,
    Zoran
    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