Hi,
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:
-
USE [RiskAnalysis]
-
GO
-
/****** Object: StoredProcedure [dbo].[usp_RiskArchive] Script Date: 04/15/2008 09:36:23 ******/
-
SET ANSI_NULLS ON
-
GO
-
SET QUOTED_IDENTIFIER ON
-
GO
-
-
ALTER PROCEDURE [dbo].[usp_RiskArchive]
-
-
AS
-
SET NOCOUNT ON
-
-
begin
-
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
-
end
-
begin
-
INSERT INTO RiskArchive (RiskID, Criticality, MitigationPlan)
-
SELECT Risk.RiskID, case when Risk.Criticality <> '' then Risk.Criticality else '' end, Risk.MitigationPlan
-
FROM Risk
-
WHERE Risk.MitigationPlan = 'None'
-
end
-
-
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
-
DoCmd.SetWarnings False
-
stUSP = "usp_RiskArchive"
-
DoCmd.OpenStoredProcedure stUSP, acViewNormal, acEdit
-
DoCmd.SetWarnings True
-
End If
-
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,
Andrew
