sql - Violation of primary key constraint error when no duplicate exists in target -

i violation of primary key constraint error on single record when try insert existing table.

the first time issue appeared within merge statement, can reproduce issue trying insert record alone through .

the primary key on varbinary(20) value generated hashbytes function on concatenation of values of approximately 20 column values. varbinary(20) value i'm trying insert not present in target table , present once in source table. when filter on actual column values, result same; 1 record in source , 0 records in target.

the target table has 101 178 878 rows. primary key not clustered index on table, there bigint identity column serving clustered index. there no triggers on target or source. collations same on (danish_norwegian_ci_as). instance sql server 2008 r2, standard edition.

i'm totally stuck, , hope has offer.

thank reading :-)

this value fails: 0x0571f5f713cf220bb0db057bbce7e158ca6f89c0

in target, following values closest 1 failing. there no value equal 1 failing.

0x0571f5f1238704e1a9d612f0a1648766b769e2ae 0x0571f5f713cf220bb0db057bbce7e158ca6f89c0 --the value fails 0x0571f608cbe12a007f3dc3736d2f03d90eccf3f7

edit: there corruption issues in database causing unexpected behaviour.

well answer definately in error, find cause (inside of transaction rollback)

try doing merge update , insert, , not jsut insert . merge in output section generate code states doing, update or insert. the sysntax merge if need it

if update , 0 rows affected fighting loosing bettel against corruption issues.

if coruption in statistics script out indexes, drop them, drop statistics still remain, generate indexes again , see if still have issue.

hope have been of help

walter