472,133 Members | 1,458 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,133 software developers and data experts.

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

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
1 5369
ck9663
2,878 Expert 2GB
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.

Similar topics

7 posts views Thread by Bill Kellaway | last post: by
6 posts views Thread by pk | last post: by
3 posts views Thread by Hai Nguyen | last post: by
8 posts views Thread by nano2k | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.