cannot insert duplicate key row in object 'Table' with unique index 'Index'

[rdsandy]

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

[ck9663]

You need to add a WHERE...NOT EXISTS on your query inside stored proc. You might also want to consider that although RiskID is already existing on your archive, other fields were updated on your risk table. Depending on your requirement, you might want to update your archive as well.

-- CK