By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,628 Members | 1,175 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,628 IT Pros & Developers. It's quick & easy.

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

P: 20
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:

Expand|Select|Wrap|Line Numbers
  1. USE [RiskAnalysis]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[usp_RiskArchive]    Script Date: 04/15/2008 09:36:23 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8.  
  9. ALTER PROCEDURE [dbo].[usp_RiskArchive]
  10.  
  11. AS
  12. SET NOCOUNT ON
  13.  
  14. begin
  15.   INSERT INTO RiskArchive (RiskID, Criticality, MitigationActionID, MitigationPlan)
  16.   SELECT Risk.RiskID, CASE WHEN Risk.Criticality <> '' THEN Risk.Criticality ELSE '' END AS Expr1, Mitigation.MitigationActionID, Risk.MitigationPlan
  17.   FROM Risk INNER JOIN
  18.     Mitigation ON Risk.RiskID = Mitigation.RiskID
  19. end
  20. begin
  21.   INSERT INTO RiskArchive (RiskID, Criticality, MitigationPlan)
  22.   SELECT     Risk.RiskID, case when Risk.Criticality <> '' then Risk.Criticality else '' end, Risk.MitigationPlan
  23.   FROM Risk
  24.   WHERE Risk.MitigationPlan = 'None'
  25. end
  26.  
  27. set nocount off
  28.  
This is to be run at the end of the month in vba using the below code on a close form button:

Expand|Select|Wrap|Line Numbers
  1. Dim stUSP As String
  2.  
  3.     If Date = DateSerial(Year(Date), Month(Date) + 1, 0) Then
  4.         DoCmd.SetWarnings False
  5.         stUSP = "usp_RiskArchive"
  6.         DoCmd.OpenStoredProcedure stUSP, acViewNormal, acEdit
  7.         DoCmd.SetWarnings True
  8.     End If
  9.  
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
Apr 15 '08 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
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
Apr 15 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.