subject: cannot insert duplicate key row in object 'Table' with unique index 'Index'
I have a table Risk, a table Mitigation and a table RiskArchive. I am taking the RiskID, Criticality and MitigationPlan fields from Risk, and MitigationActionID from Mitigation and inserting them into RiskArchive, using a stored procedure. What happens is if there is no entry in RiskArchive for a specific RiskID, and the other fields relevant for it, then it needs to be archived, but also only if it is not duplicated.
I have made an index in RiskArchive with RiskID, Criticality and MitigationActionID as the columns and put them as unique. RiskID is a unique field in the Risk table, so is MitigationActionID in Mitigation table, but as there can be many MitigationActionID's to each RiskID, then the RiskID cannot be a unique field itself in RiskArchive.
Here is the stored procedure I have:
/****** Object: StoredProcedure [dbo].[usp_RiskArchive] Script Date: 04/15/2008 09:36:23 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[usp_RiskArchive]
SET NOCOUNT ON
INSERT INTO RiskArchive (RiskID, Criticality, MitigationActionID, MitigationPlan)
SELECT Risk.RiskID, CASE WHEN Risk.Criticality <> '' THEN Risk.Criticality ELSE '' END AS Expr1, Mitigation.MitigationActionID, Risk.MitigationPlan
FROM Risk INNER JOIN
Mitigation ON Risk.RiskID = Mitigation.RiskID
INSERT INTO RiskArchive (RiskID, Criticality, MitigationPlan)
SELECT Risk.RiskID, case when Risk.Criticality <> '' then Risk.Criticality else '' end, Risk.MitigationPlan
WHERE Risk.MitigationPlan = 'None'
set nocount off
This is to be run at the end of the month in vba using the below code on a close form button:
Dim stUSP As String
If Date = DateSerial(Year(Date), Month(Date) + 1, 0) Then
stUSP = "usp_RiskArchive"
DoCmd.OpenStoredProcedure stUSP, acViewNormal, acEdit
It works the first time it is run, but if it is run again I get the error:
"Cannot insert duplicate key row in object 'dbo.RiskArchive' with unique index 'IX_RiskArchive2'"
I know it is trying to insert the same rows again, so what I want is to run the stored procedure and only insert rows that arent already in RiskArchive (even if it is just the Criticality field that has changed). I just tried adding a new row and tried to close the form, but that row wasn't added and the error appeared.
Many thanks in advance,