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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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