March 25, 2013 at 2:37 pm
Hello everyone.
I hope someone can confirm or deny my thinking here an offer some advice. Here is my issue.
I do a daily load into a staging table.
The staging table then loads the data into the main table.
On successful completion the staging table is truncated at end of my SP ready for next load.
Next day runs again , etc , etc.
Now getting a error.
error 2627 - PK violation.
Violation of PRIMARY KEY constraint 'PK_DandD1'.
Cannot insert duplicate key in object 'XXX.DandD'. The duplicate key value is (4714280, 1117455).
I have a Composite PK which is made up of the 2 values listed.
On checking the destination table I can see an entry already for that PK , so my insertion from the staging table fails.
My questions are, and I hope i know the answer already , just need it sanity checked.
1. If my insertion fails for 1 row, all insertions contained in the staging table will fail. ( the rest do not seem to voilate the PK)
2. If I remove the 1 row in the destination table , all my subsequent insertions from staging table that do not voilate the PK will work ? - then investigate violation reasons etc.
Does that make sense ?
March 25, 2013 at 3:04 pm
The answer to both questions is yes.
However, you could also do the insert so it does not fail on the PK violation:
insert into MyTable
select
MyStage.*
from
MyStage
left outer join
MyTable
on
MyStage.PK_Col1 = MyTable.PK_Col1 and
MyStage.PK_Col2 = MyTable.PK_Col2
where
MyTable.PK_Col1 is null and MyTable.PK_Col2 is null
March 25, 2013 at 3:19 pm
Thanks for the quick reply
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply