Deploy Violation of UNIQUE KEY constraint

PrgSkidmark

I keep getting Cannot insert duplicate key. I changed the comparison to use the unique key.
For Example, I tried to compare a table, and it says there are 3000 "source only" records. I get cannot insert duplicate key. So I copy the key out of the deploy script, and it deletes 188 rows. Why is the SQL Data compare telling me these are all "source only" when it deletes 188 rows with that key.
So I'm trying a different table, and again I switch to the the unique key. There are 15000 rows "source only", but I get cannot insert duplicate key. This deploy script is so massive that I cannot copy the key out of the script and delete rows.
How can I make this SQL Data Compare only give me NEW rows, or actually ignore this existing row?

PrgSkidmark

I updated to the 11.6.3.1926 yesterday morning.
I had to get those records copied yesterday. I found I could export comparison results.
I opened the table.csv file in excel, did text to columns, and copied the key to a new query. I deleted all the rows from target table where key in that list. And it deleted almost 260. So out of 15000 rows almost 260 keys existed in target, but the SQL Data Compare said they were source only.
If you look at the Source Only Panel at the bottom, you'll even see the Source has value and Target has the shaded box.

I even tried removing all fields from columns in comparison. So 2 key fields compared to ONLY those 2 key fields. I got exactly the same result. The difference is that the insert script only has those 2 fields in the insert statements.

The field types I am comparing are UNIQUEIDENTIFIER and DATETIME2.
I thought that perhaps the compare logic doesn't know how to compare them.
So I tried just the UNIQUEIDENTIFIER, which is the key. It also doesn't work. I gathered some screen shots to show the comparison key is the PK of TranID. I check source only and show that it shows the record as source only, and not as a difference.


I try to deploy and get the error.

Then I show the existance of the key in the source table.


The 3000 record compare was using an IDENTITY column of type BIGINT. Its "Source Only" was wrong when a delete query on the key deleted 188.

PrgSkidmark

I needed to do a vehicle copy, and I got some screen shots. Out of 533 "Source Only" vehicles, there are actually 55 vehicles that exist in the target by the PK which is used to compare. So these 55 vehicles are NOT put into the different category, and are considered "Source Only"
Here is the comparison key

You can see it puts these in Source Only and shows no target field data

Here is a target table query with a selection of the existance of these keys


The PK is just a BIGINT and INT. I'm not sure why the compare is failing to recognize they exist by key.

PrgSkidmark

I figured out why this is happening.
The WHERE clause which defaults to both source and target are using the an additional Field SaleDT. Those 55 vehicles in the Target had a SaleDT that returns false for the target where, so they're excluded when the results are compared. That makes it look like there are 55 "Source Only" vehicles.

So this brings up a wish list item.
Given the PK is used it should not be allowed to determine the key is "Source Only" based on the WHERE clause I supplied.