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
 Old Forums
 CLOSED - General SQL Server
 URGENT - while will this not insert?

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,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 - 2005-09-21 : 10:49:20
lets see a script out of the table including 'PK_tblCapRec'
Go to Top of Page

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]
GO




Brenda

If it weren't for you guys, where would I be?
Go to Top of Page

bertcord
Starting Member

7 Posts

Posted - 2005-09-21 : 13:29:31
does table tblCapRecBeforeChanges have a duplicate of [CaseNumber],[PartNumber] ?

Go to Top of Page

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-09-21 : 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?
Go to Top of Page
   

- Advertisement -