Please explain why an update is not performed

dwainewdwainew Posts: 59 Bronze 4
edited January 18, 2016 11:25AM in SQL Data Compare 11
We are trying to update target data between systems that don't have matching IDENTITY IDs. We're selecting columns known to be unique, but that don't necessarily have unique constraints. SDC is generating delete/insert statements and the following message in script:

"Delete and re-insert row in <tablename> due to identity row modification with non-unique comparison key"

The question is, why is a delete/insert being performed even though the ID is known as it is clearly used after SET IDENTITY_INSERT ON in the following insert statements?

Is this decision performance related, stability related, or driven by some requirements I haven't thought of?

Some here are uncomfortable about deleting records only to insert later, as opposed to updating in place (I realize SQL is essentially doing the same thing, a page being the smallest unit of IO).

Comments

  • Hello Dwaine,

    Sorry for the issue you are having with SQL Data Compare. We've opened a support ticket for this issue so we can get to the bottom of why this is happening. I'll be responding through that for some follow up info we need to start troubleshooting, thanks.
    Allen LeVan
    Red Gate Software
    US Product Support
Sign In or Register to comment.