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.
September 16, 2021 at 12:22 pm
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct 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