Violation of PRIMARY KEY constraintCannot insert duplicate key in.

Question

Violation of PRIMARY KEY constraintCannot insert duplicate key in.

rosyshrestha7

Old Hand

Points: 342
More actions
January 15, 2009 at 11:12 am

#198488

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Description:DTS Queries
-- =============================================
ALTER PROCEDURE [dbo].[dts_load_t_pam_mgr_cash_transactions]
--Parameters
--No Params
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRY
delete from pam.dbo.t_pam_transactions_lots
where LedgerDate between
staging.[dbo].[GetFirstDayOfMonth] ( (select max(ledgerdate) from pam.[dbo].[t_pam_transactions_lots] ))
and (select max(ledgerdate) from pam.[dbo].[t_pam_transactions_lots] )and TimCloseStatus is null and cusip='000009USD'
INSERT INTO [PAM].[dbo].[t_pam_transactions_lots]
([AccBasis]
,[CurrBasis]
,[TransactNum]
,[PortAccId]
,[IssuerID]
,[TransType]
,[Cusip]
,[TradeDate]
,[SettleDate]
,[Cost]
,[Cash]
,[SettleCash]
,[InvestPrgm]
,[LotNum]
,[MgrGrp]
,[StampDate]
,[LedgerDate]
,[DateLoaded])
Select
AccBasis
,CurrBasis
,TransactNum
,PortAccId
,'000009'
,TransType
,'000009USD'
,TradeDate
,SettleDate
,Cost
,Cash
,SettleCash
,InvestPrgm
,0
,MgrGrp
,StampDate
,LedgerDate
,getdate()
from staging.dbo.t_pam_mgr_cash_transactions
END TRY
BEGIN CATCH
EXEC spLogError
END CATCH;
END TRY
BEGIN CATCH
--EXEC spLogError
END CATCH;
GRANT EXECUTE ON dts_load_t_pam_mgr_cash_transactions TO PUBLIC
END
when executing Stored procedured i am getting error: Violation of PRIMARY KEY constraint 'PK_t_pam_transactions_lots'. Cannot insert duplicate key in object 'dbo.t_pam_transactions_lots'.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

What is the primary key definition? The error is pretty self-explanatory. You are getting dupes, so either you have bad data or a poorly chosen primary key.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

By looking at your code, we really can't tell what the problem is. Your problem is also related to the data as apparently you are trying to insert data that already exists in the database.

To really be able to assist you we need more information. Please read the article that I have linked at the top of my signature block below. It will show you what we need to be able to help you and how to create and post that information.