Which record is being duplicate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Which record is being duplicate

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-12-07 : 16:00:40

Hi All

I am trying to insert the data by using below query

INSERT INTO [dbo].[Enrollment]([SubID],[LineContractNbr],[LineContractIndex],[AnshinPlusID],[ContractCompanyCd]
,[SubscriberNbr],[LineContractDt],[PostalCd],[SubscriberType],[SubscriberDOB],[EnrollDt],[CancelDt],[HandsetCd],[HandsetName],[ManufNbr]
,[POSCd],[POSExtendedCd],[POSName],[PurchaseDt],[ContractDt],[MailDt],[WarrantyActivationDt],[AddresseeIdentifier],[RestartDt],[DataCreateDttm]
,[Active],[Source],[LoadKey],[FDate],[ClientId],[ClientProgramID],[EnrHash],[MarketID],[Status],[InsertDttm],[UpdateDttm],[ReportingPointer]
,[CountryID],[MiscUseI1],[MiscUseI2],[MiscUseC1],[MiscUseC2],[EditFlag],[AsurionSKU],[CommittedPOSCode],[CommittedPOSExtendedCode]
,[CommittedPOSName],[AppliedPOSCode],[AppliedPOSExtendedCode],[AppliedPOSName],[ServicePlan],[OrderType],[ServicePlanName],[Course]
,[ContractType],[MiniSimFlag],[UIMCardVersion],[HandsetRegistrationDt],[KHPointUsedDt],[CourseStartDt],[RecID])

SELECT [SubID],EB.[LineContractNbr],[LineContractIndex],[AnshinPlusID],[ContractCompanyCd]
,[SubscriberNbr],[LineContractDt],[PostalCd],[SubscriberType],[SubscriberDOB],[EnrollDt],[CancelDt],[HandsetCd],[HandsetName],[ManufNbr]
,[POSCd],[POSExtendedCd],[POSName],[PurchaseDt],[ContractDt],[MailDt],[WarrantyActivationDt],[AddresseeIdentifier],[RestartDt],[DataCreateDttm]
,[Active],[Source],[LoadKey],[FDate],[ClientId],[ClientProgramID],[EnrHash],[MarketID],[Status],[InsertDttm],[UpdateDttm],[ReportingPointer]
,[CountryID],[MiscUseI1],[MiscUseI2],[MiscUseC1],[MiscUseC2],[EditFlag],[AsurionSKU],[CommittedPOSCode],[CommittedPOSExtendedCode]
,[CommittedPOSName],[AppliedPOSCode],[AppliedPOSExtendedCode],[AppliedPOSName],[ServicePlan],[OrderType],[ServicePlanName],[Course]
,[ContractType],[MiniSimFlag],[UIMCardVersion],[HandsetRegistrationDt],[KHPointUsedDt],[CourseStartDt],[RecID]
FROM [dbo].[Enrollment_Base] EB (NOLOCK)
JOIN Stage.LCNsToReprocess R (NOLOCK)
ON EB.LineContractNbr=R.LineContractNbr

But this is throwing me error
Msg 2627, Level 14, State 1, Procedure IJEnrollDataCorrection, Line 42
Violation of PRIMARY KEY constraint 'PK_Enrollment'. Cannot insert duplicate key in object 'dbo.Enrollment'.
The statement has been terminated

SubID is a primary key in 'dbo.Enrollment' table and this table is having 41367783 records already .My problem is that I need to find out that records which is being inserted 'dbo.Enrollment' table due to that error is occurring .Please let me know how can i find out that record.

Thanks
VIjay sahu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-07 : 16:03:21

select * from Enrollment e where exists (
SELECT *
FROM [dbo].[Enrollment_Base] EB (NOLOCK)
JOIN Stage.LCNsToReprocess R (NOLOCK)
ON EB.LineContractNbr=R.LineContractNbr where eb.subid = e.subid)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-12-07 : 16:18:48

Thanks it is working correctly what I wanted to get.

Thank you very much.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-07 : 16:24:54

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources