| Author |
 Topic  |
|
|
brendalisalowe
Constraint Violating Yak Guru
269 Posts |
 Posted - 09/21/2005 : 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,Credit
FROM 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!
Brenda
If it weren't for you guys, where would I be?
|
|
|
bertcord
Starting Member
7 Posts |
Posted - 09/21/2005 : 10:49:20
|
| lets see a script out of the table including 'PK_tblCapRec' |
 |
|
|
brendalisalowe
Constraint Violating Yak Guru
269 Posts |
Posted - 09/21/2005 : 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]
GO
Brenda
If it weren't for you guys, where would I be?
|
|
|
|
bertcord
Starting Member
7 Posts |
Posted - 09/21/2005 : 13:29:31
|
does table tblCapRecBeforeChanges have a duplicate of [CaseNumber],[PartNumber] ?
|
|
|
|
brendalisalowe
Constraint Violating Yak Guru
269 Posts |
Posted - 09/21/2005 : 14:11:00
|
I ran this:
select tblCapRecBeforeChanges.CaseNumber,tblCapRecBeforeChanges.PartNumber from tblCapRecBeforeChanges
join
(select casenumber,partnumber from tblCapRecBeforeChanges
group by casenumber, partnumber
having count(*) > 1 ) as dups
on tblCapRecBeforeChanges.casenumber = dups.casenumber and tblCapRecBeforeChanges.partnumber = dups.partnumber
order by tblCapRecBeforeChanges.casenumber
YES, there aer duplicates. How can I get rid of them?
Brenda
If it weren't for you guys, where would I be?
|
|
|
| |
Topic |
|
|
|
URGENT - while will this not insert?
Forum Locked
