Please explain why an update is not performed
dwainew
Posts: 59 Bronze 4
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).
"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
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.
Red Gate Software
US Product Support