locked
Identity and Migration Process RRS feed

  • Question

  • User-1821287852 posted

    Hi all, 

    I have a system with a main table in which the primary key is an identity. This is working fine, the problem is that now I have to migrate data from an older system and my customer wants to keep their old ID's. I developed a C# application to parse an excel file exported by the other system, and insert the old data in the database. I have set IDENTITY_INSERT to "ON" on the table but it is not working within my C# code from the migration app. I also tried setting it to "ON" via SQL Query before running the application but it also keeps throwing an error message regarding the IDENTITY_INSERT.

    Is there any workaround for this without having to remove my identity property from the table's primary key? 

    Thanks.

    Thursday, February 11, 2016 3:47 PM

Answers

  • User-1821287852 posted

    I removed the identity permanently. 

    Tks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 16, 2016 1:45 PM

All replies

  • User753101303 posted

    Hi,

    It won't work when running that before running the application as it is valid for the current SQL session. What happens when you try to run that from within your application? Could it be that the account used by your app is not allowed to use this statement?

    I would likely use a console app to do that.

    Not directly related but AFAIK the id should not even be visible to users. Not sure to get why they want to keep it unchanged (or is this that they want to keep something else or that they wan't also to import related data as well?). Also more likely you'll have some collision. Usually you'll add a number to the id to avoid collision ie if the last id in the new system is 800 you'll insert for example oldid+1000  to avoid collisions.

    Friday, February 12, 2016 12:33 AM
  • User-1821287852 posted

    I removed the identity permanently. 

    Tks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 16, 2016 1:45 PM