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).
Hello Dwaine,
"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).