473,379 Members | 1,260 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,379 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 5585
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

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

Similar topics

7
by: Bill Kellaway | last post by:
Hi there - this should be fairly simple for someone. Basically I can't figure out how to pass the parameters from ASP to a Stored Procedure on SQL. Here's my code: I just need to help in...
2
by: Caroline | last post by:
I want to add the content of a table into another I tried to copy all fields, except the primary key: INSERT INTO table2 (field2, field3, field4, ...) SELECT field2, field3, field4, ... FROM ...
6
by: pk | last post by:
Sorry for the piece-by-piece nature of this post, I moved it from a dormant group to this one and it was 3 separate posts in the other group. Anyway... I'm trying to bulk insert a text file of...
8
by: Kragen Sitaker | last post by:
ERROR: Cannot insert a duplicate key into unique index pg_class_relname_nsp_index We've been getting this error in our application every once in a while --- typically once an hour to once a day,...
10
by: florian | last post by:
Hi, we have a contention problem because of an application which tries to insert a duplicate row in a table with primary key. This insert fails of course but the locks are not released within...
3
by: Hai Nguyen | last post by:
Hi all I was attempting to insert multiple row by using a loop into a database.A table has 2 primary keys and one regular field (PR) (PR) ID Project Ans 1 2 a 1 ...
8
by: petebeatty | last post by:
I have created a SQL string the properly inserts a record in the table. However, the insert does not occur at the end of the table. Instead it inserts a record after the last record that I viewed....
8
by: nano2k | last post by:
Hi Shortly, I keep invoices in a table. Occasionally, someone will fire the execution of a stored procedure (SP) that performs several UPDATEs against (potentially) all invoices OLDER than a...
2
by: Weyus | last post by:
All, Just want to make sure that I understand what's going on here. I have a table with IGNORE_DUP_KEY set on a unique, multi-column index. What I'm seeing is this: 1) When performing a...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

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.