Violation of primary key constraint error if there is no duplicate in the target

Question

I get a violation of the primary key constraint error in one record when I try to insert it into an existing table.

The first time a problem occurred, it was included in the merge statement, but I can reproduce the problem trying to insert the record myself.

The primary key is in the varbinary (20) value generated by the hash byte function when the values ​​of approximately 20 column values ​​are concatenated. The varbinary (20) value I'm trying to insert is not in the target table, and it is only present once in the source table. When I filter the actual column values, the result will be the same; one record in the source and zero records in the target.

The goal table contains 101 178 878 rows. The primary key is not a clustered index in the table; there is a bigint identifier column that serves as a clustered index. There are no triggers on the target or source. The mappings are the same in everything (Danish_Norwegian_CI_AS). Instance - SQL Server 2008 R2 Standard Edition.

Now I'm completely stuck and I hope someone can help.

Thanks for reading: -)

This value is not satisfied: 0x0571F5F713CF220BB0DB057BBCE7E158CA6F89C0

In the target, the ones closest to one of them are as follows. There is no value equal to one.

0x0571F5F1238704E1A9D612F0A1648766B769E2AE 0x0571F5F713CF220BB0DB057BBCE7E158CA6F89C0 - a value that does not work 0x0571F608CBE12A007F3DC3736D2F03D90EC

EDIT: There are corruption issues in the database that cause unexpected behavior.

Answer 1

Well, your answer is definitely in error, To find the reason I would do (inside a rollback transaction)

Try merging with update and insert rather than jsut insert. When merging in the OUTPUT section, you generate code that indicates what you are actually doing, updating or pasting. sysntax to merge if you need it

if you get an update and 0 lines are affected, you are struggling with a bettel loss against your corruption problems.

If the coruption is in the statistics, then the script output the indexes, leave them, discard the remaining statistics, generate the indexes again and see if you have a problem.

Hope for help

Walter