July 25, 2014 at 4:31 pm
I need to insert some rows from a temporary table into a persistent table. The persistent table has a primary key composed of three columns, so I have to make sure I don't insert any combination of the three columns that already exists in the persistent table.
I've built protection into the SELECT statement to prevent inserting duplicates by doing an outer join from the source table to the destination table. Pretty standard stuff...except it's not working.
INSERT INTO dbo.tblSpot_Used_Counts
(
SR_ID
,IR_ID
,Ordernr
,Used_Count
,Used_Count_Autofill
)
SELECT
u.SR_ID
,u.IR_ID
,u.Ordernr
,u.LocalTimesUsed
,u.LocalTimesUsedAF
FROM #Usage u
LEFT OUTER JOIN tblSpot_Used_Counts tsc ON
tsc.SR_ID = u.SR_ID
AND tsc.IR_ID = u.IR_ID
AND tsc.Ordernr = u.Ordernr
WHERE tsc.Used_Count IS NULL
I've verified in the data that in fact no duplicates are being created.
I've dropped the primary key on the target table and replaced it with a unique constraint, but that gives me an error that says the unique constraint is being violated.
If I then drop that constraint and call the stored proc, the insert doesn't produce an error. If I then query the data, it shows that there are no repeated combinations of SR_ID, IR_ID and Ordernr.
So we're getting primary key/unique constraint errors when we're not actually violating the primary key/unique constraint.
I've got to get past this in a hurry, but I'm completely stumped. Anybody got any ideas?
July 26, 2014 at 1:17 am
A quick thougt, any difference in the select if you add the DISTINCT directive?
😎
July 26, 2014 at 2:54 am
If you're getting that error, you're either trying to insert a row that already exists or there are duplicates in the set you're inserting. There's no other reasons you'd be getting a pk violation.
Try using a NOT EXISTS instead of the join. Should be equivalent, but a not exists is harder to mes up (eg if the column you're checking for null is nullable itself)
Check this to see if there are dups in the source data:
SELECT
u.SR_ID
,u.IR_ID
,u.Ordernr
,count(*)
FROM #Usage u
group by u.SR_ID
,u.IR_ID
,u.Ordernr
order by count(*) desc
What are the data types involved?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply