SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 URGENT - while will this not insert?
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 09/21/2005 :  10:45:28  Show Profile
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  Show Profile
lets see a script out of the table including 'PK_tblCapRec'
Go to Top of Page

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 09/21/2005 :  10:59:51  Show Profile
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 - 09/21/2005 :  13:29:31  Show Profile
does table tblCapRecBeforeChanges have a duplicate of [CaseNumber],[PartNumber] ?

Go to Top of Page

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 09/21/2005 :  14:11:00  Show Profile
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
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000