Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
| Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 15th, 2008, 05:03 AM
Registered User
 
Join Date: Apr 2008
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default cannot insert duplicate key row in object 'Table'

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

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:

Code:
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:

Code:
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



Similar Threads
Thread Thread Starter Forum Replies Last Post
Cannot insert duplicate key row in object 'dbo.mov alxGramma SQL Server 2005 4 April 15th, 2008 01:42 PM
insert a duplicate row with one changed field only swdgaurav SQL Language 1 December 31st, 2007 04:54 AM
insert row into table stealthdevil Access VBA 11 June 16th, 2007 08:32 AM
Insert New Row into Table (VB 2005) adit9 Visual Basic 2005 Basics 0 March 3rd, 2006 03:25 AM
How to duplicate 1 row in table minhpx SQL Language 1 December 20th, 2004 09:51 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.