insert gives duplicate key violation when select by key gives zero results

  • There is a table mr_patients with a primary key constraint on a column MRN numeric(10,0)  (I did not design the database).

    when i do "select * from mr_patients where mrn = 12442" i get zero results

    when i do "insert into mr_patients (mrn, plus other columns) select mrn, plus other columns from <anothertable> where anothertable.mrn = 12442"  I get a primary key violation that specifies 12442 as the duplicate key.

    I have tried dbcc checkdb and specifically check table and reindex all to no avail.

    I am looking for other ideas.  This is SQLServer express 2019 (15.0.2000)

  • I found the problem.  I needed a top 1 in the select statement, it was returning multiple records.  Forehead slapper.  Duh.

  • Thanks for posting the solution. It's helpful to the next person who looks at the question.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply