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 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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