Author |
Topic |
brendalisalowe
Constraint Violating Yak Guru
269 Posts |
Posted - 2005-09-21 : 10:45:28
|
INSERT INTO tblCapRec (CaseNumber,PartNumber,Status, LastName,FirstName,Others,SSN,OtherSSN,OtherNotes,InGDB,DocsOrdered, DocsHere,AppHere,Ubum,Suspended,Trace,SentToHUD,SpanishCase,MailAddress, MailAddress2,MailCity,MailState,MailZipCode,CareOf,HomePhone,WorkPhone, CellPhone,FaxPhone,OtherPhone,SpousePhone,SpouseWork,SpouseCell,SpouseOther, PropAddress,PropCity,PropState,PropZipCode,Updated,MailKey,MailedFirst,MortgageAmt, PaidUpFront,EndorseDate,Term,MatureDate,EncumDate,HoldingMor,ServingMor,RcvdFromOld,RcvdFromRegular,RcvdFromRtn,RcvdFrom2s,RefundAmt,RateCharged, FeeAmount,AfterFee,AmtPaid,LateFees,RemainingDue,Sold,Refinanced,Foreclosed,ApproxDate, Cashed,Returned,NoInfo,HaveSuspLtrYes,HaveSuspLtrNo,RegularBilling, Final,TSI,Credit) SELECT CaseNumber,PartNumber,Status,LastName,FirstName,Others,SSN,OtherSSN, OtherNotes,InGDB,DocsOrdered,DocsHere,AppHere,Ubum,Suspended,Trace,SentToHUD, SpanishCase,MailAddress,MailAddress2,MailCity,MailState,MailZipCode,CareOf,HomePhone, WorkPhone,CellPhone,FaxPhone,OtherPhone,SpousePhone,SpouseWork,SpouseCell,SpouseOther, PropAddress,PropCity,PropState,PropZipCode,Updated,MailKey,MailedFirst,MortgageAmt, PaidUpFront,EndorseDate,Term,MatureDate,EncumDate,HoldingMor,ServingMor,RcvdFromOld, RcvdFromRegular,RcvdFromRtn,RcvdFrom2s,RefundAmt,RateCharged,FeeAmount,AfterFee, AmtPaid,LateFees,RemainingDue,Sold,Refinanced,Foreclosed,ApproxDate, Cashed,Returned,NoInfo,HaveSuspLtrYes,HaveSuspLtrNo,RegularBilling, Final,TSI,CreditFROM tblCapRecBeforeChanges WHERE CaseNumber Not In (SELECT DISTINCT CaseNumber FROM tblCapRec)I get the old error: (Violation of PRIMARY KEY constraint 'PK_tblCapRec'. Cannot insert duplicate key in object 'tblCapRec'.The statement has been terminated.)Wouldn't the WHERE statement take care of that? Please help ASAP if you can. THanks!BrendaIf it weren't for you guys, where would I be? |
|
bertcord
Starting Member
7 Posts |
Posted - 2005-09-21 : 10:49:20
|
lets see a script out of the table including 'PK_tblCapRec' |
|
|
brendalisalowe
Constraint Violating Yak Guru
269 Posts |
Posted - 2005-09-21 : 10:59:51
|
Sorry, it is long:CREATE TABLE [tblCapRec] ( [CaseNumber] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PartNumber] [smallint] NOT NULL , [Status] [smallint] NULL , [LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Others] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [SSN] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [OtherSSN] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [OtherNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [InGDB] [bit] NULL , [DocsOrdered] [bit] NULL , [DocsHere] [bit] NULL , [AppHere] [bit] NULL , [Ubum] [bit] NULL , [Suspended] [bit] NULL , [Trace] [bit] NULL , [SentToHUD] [bit] NULL , [SpanishCase] [bit] NULL , [MailAddress] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MailAddress2] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MailCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MailState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MailZipCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CareOf] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [HomePhone] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [WorkPhone] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CellPhone] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [FaxPhone] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [OtherPhone] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [SpousePhone] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [SpouseWork] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [SpouseCell] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [SpouseOther] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PropAddress] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PropCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PropState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PropZipCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Updated] [datetime] NULL , [MailKey] [smallint] NULL , [MailedFirst] [datetime] NULL , [FirstRcvd] [datetime] NULL , [AppSent] [datetime] NULL , [AppSent2] [datetime] NULL , [AppSent3] [datetime] NULL , [AppSent4] [datetime] NULL , [AppSent5] [datetime] NULL , [OrderedDocs] [datetime] NULL , [AppRcvd] [datetime] NULL , [Paid] [datetime] NULL , [FirstBillSent] [datetime] NULL , [SecondBillSent] [datetime] NULL , [FinalBillSent] [datetime] NULL , [FeePaid] [datetime] NULL , [MortgageAmt] [float] NULL , [PaidUpFront] [float] NULL , [EndorseDate] [datetime] NULL , [Term] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MatureDate] [datetime] NULL , [EncumDate] [datetime] NULL , [HoldingMor] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ServingMor] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [RcvdFromOld] [bit] NULL , [RcvdFromRegular] [bit] NULL , [RcvdFromRtn] [bit] NULL , [RcvdFrom2s] [bit] NULL , [RcvdText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [RefundAmt] [float] NULL , [RateCharged] [float] NULL , [FeeAmount] [float] NULL , [AfterFee] [float] NULL , [AmtPaid] [float] NULL , [LateFees] [float] NULL , [RemainingDue] [float] NULL , [Contract] [image] NULL , [TFC] [image] NULL , [Application] [image] NULL , [Docs] [image] NULL , [Other] [image] NULL , [Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [NotesText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Sold] [bit] NULL , [Refinanced] [bit] NULL , [Foreclosed] [bit] NULL , [ApproxDate] [datetime] NULL , [DiffName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [OtherPeople] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PropCounty] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DocsOther] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [TraceDate] [datetime] NULL , [Operator] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Cashed] [bit] NULL , [Returned] [bit] NULL , [NoInfo] [bit] NULL , [DateSusp] [datetime] NULL , [ReasonSusp] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Certifier] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [HaveSuspLtrYes] [bit] NULL , [HaveSuspLtrNo] [bit] NULL , [RegularBilling] [bit] NULL , [Final] [bit] NULL , [TSI] [bit] NULL , [Credit] [bit] NULL , [PaymentNotes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PaymentDue] [datetime] NULL , CONSTRAINT [PK_tblCapRec] PRIMARY KEY CLUSTERED ( [CaseNumber], [PartNumber] ) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOBrendaIf it weren't for you guys, where would I be? |
|
|
bertcord
Starting Member
7 Posts |
Posted - 2005-09-21 : 13:29:31
|
does table tblCapRecBeforeChanges have a duplicate of [CaseNumber],[PartNumber] ? |
|
|
brendalisalowe
Constraint Violating Yak Guru
269 Posts |
Posted - 2005-09-21 : 14:11:00
|
I ran this:select tblCapRecBeforeChanges.CaseNumber,tblCapRecBeforeChanges.PartNumber from tblCapRecBeforeChangesjoin(select casenumber,partnumber from tblCapRecBeforeChangesgroup by casenumber, partnumber having count(*) > 1 ) as dupson tblCapRecBeforeChanges.casenumber = dups.casenumber and tblCapRecBeforeChanges.partnumber = dups.partnumberorder by tblCapRecBeforeChanges.casenumberYES, there aer duplicates. How can I get rid of them?BrendaIf it weren't for you guys, where would I be? |
|
|
|
|
|