August 19, 2004 at 1:08 pm
Here is my dilema. I want to take data from two different tables and insert it into one table, if that case doesn't already exist. This is what I have so far, but it doesn't work right:
IF NOT EXISTS(SELECT * from tblCapRec) INSERT INTO tblCapRec (CaseNumber,PartNumber,Status,LastName,FirstName,Others,SSN,OtherSSN,OtherNotes,InGDB,DocsOrdered,DocsHere,AppHere,Suspended,SentToHUD,SentToTSI,SentToCredit,SpanishCase,MailAddress,MailAddress2,MailCity,MailState,MailZipCode,CareOf,HomePhone,WorkPhone,CellPhone,FaxPhone,OtherPhone,SpousePhone,SpouseWork,SpouseCell,SpouseOther,PropAddress,PropCity,PropState,PropZipCode,Updated,MailKey,MailedFirst,FirstRcvd,AppSent,AppSent2,AppSent3,AppSent4,AppSent5,OrderedDocs,AppRcvd,Paid,FirstBillSent,SecondBillSent,FinalBillSent,FeePaid,MortgageAmt,PaidUpFront,EndorseDate,Term,MatureDate,EncumDate,HoldingMor,ServingMor,RcvdFromOld,RcvdFromRegular,RcvdFromRtn,RcvdFrom2s,RcvdText,RefundAmt,RateCharged,AmtPaid,LateFees,Contract,TFC,Application,Docs,Other,Notes) SELECT t1.CaseNumber,1,1,t1.Lastname,t1.FirstName,NULL,t2.akas_ssn_1,t2.akas_ssn_2,t2.subj_first_1+t2.subj_middle_1+t2.subj_last_1+t2.subj_suffix_1,0,0,0,0,0,0,0,0,0,t2.subj_address_1,NULL,t2.subj_city_1,t2.subj_state_1,t2.subj_zipcode_1,NULL,t2.subj_phone10_1,subj_phone10_2,NULL,NULL,t2.subj_phone10_3,NULL,NULL,NULL,NULL,t1.PropAddress,t1.PropCity,t1.PropState,t1.PropZipCode,GETDATE(),1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,t1.MortAmount,t1.PaidUpFront,t1.EndorseMonth+'/'+t1.EndorseDay+'/'+t1.EndorseYear,t1.Term,t1.MaturityMonth+'/'+t1.MaturityDay+'/'+t1.MaturityYear,t1.EncumMonth+'/'+t1.EncumDay+'/'+t1.EncumYear,t1.HoldingMor,t1.ServingMor,0,0,0,0,NULL,t1.RefundAmount,t1.RateCharged,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM tblAbove1500 as t1 INNER JOIN tblAccurint_Above1500 as t2 ON t1.CaseNumber = t2.acctno
When tblCapRec is empty this will work, but otherwise it won't with new data. What am I doing wrong? If I haven't been clear, ask me questions. Thanks for your time!!!
Brenda
August 19, 2004 at 1:49 pm
"IF NOT EXISTS(SELECT * from tblCapRec)" is only true if tblCapRec is empty. If your intent is to add rows
to tblCapRec that aren't already there, you'll have to add a 'WHERE NOT EXISTS...' to your SELECT.
August 19, 2004 at 2:01 pm
Where do I put the "'WHERE NOT EXISTS"? Can you show me? Thanks!
August 19, 2004 at 2:06 pm
<code snipped>...INNER JOIN tblAccurint_Above1500 as t2 ON t1.CaseNumber = t2.acctno WHERE NOT EXISTS ( Select * FROM tblCapRec WHERE ...)
I don't know what your criteria are for inclusion/exclusion, so I can't help with that part. I'm just guessing it's based on what's already in tblCapRec .
August 19, 2004 at 2:13 pm
Do I have to have a WHERE clause at the end or can I just do this:
IF NOT EXISTS(SELECT * from tblCapRec) INSERT INTO tblCapRec (CaseNumber,PartNumber,Status,LastName,FirstName,Others,SSN,OtherSSN,OtherNotes,InGDB,DocsOrdered,DocsHere,AppHere,Suspended,SentToHUD,SentToTSI,SentToCredit,SpanishCase,MailAddress,MailAddress2,MailCity,MailState,MailZipCode,CareOf,HomePhone,WorkPhone,CellPhone,FaxPhone,OtherPhone,SpousePhone,SpouseWork,SpouseCell,SpouseOther,PropAddress,PropCity,PropState,PropZipCode,Updated,MailKey,MailedFirst,FirstRcvd,AppSent,AppSent2,AppSent3,AppSent4,AppSent5,OrderedDocs,AppRcvd,Paid,FirstBillSent,SecondBillSent,FinalBillSent,FeePaid,MortgageAmt,PaidUpFront,EndorseDate,Term,MatureDate,EncumDate,HoldingMor,ServingMor,RcvdFromOld,RcvdFromRegular,RcvdFromRtn,RcvdFrom2s,RcvdText,RefundAmt,RateCharged,AmtPaid,LateFees,Contract,TFC,Application,Docs,Other,Notes) SELECT t1.CaseNumber,1,1,t1.Lastname,t1.FirstName,NULL,t2.akas_ssn_1,t2.akas_ssn_2,t2.subj_first_1+t2.subj_middle_1+t2.subj_last_1+t2.subj_suffix_1,0,0,0,0,0,0,0,0,0,t2.subj_address_1,NULL,t2.subj_city_1,t2.subj_state_1,t2.subj_zipcode_1,NULL,t2.subj_phone10_1,subj_phone10_2,NULL,NULL,t2.subj_phone10_3,NULL,NULL,NULL,NULL,t1.PropAddress,t1.PropCity,t1.PropState,t1.PropZipCode,GETDATE(),1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,t1.MortAmount,t1.PaidUpFront,t1.EndorseMonth+'/'+t1.EndorseDay+'/'+t1.EndorseYear,t1.Term,t1.MaturityMonth+'/'+t1.MaturityDay+'/'+t1.MaturityYear,t1.EncumMonth+'/'+t1.EncumDay+'/'+t1.EncumYear,t1.HoldingMor,t1.ServingMor,0,0,0,0,NULL,t1.RefundAmount,t1.RateCharged,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM tblAbove1500 as t1 INNER JOIN tblAccurint_Above1500 as t2 ON t1.CaseNumber = t2.acctno WHERE NOT EXISTS (Select * FROM tblCapRec)
Will that work?
August 19, 2004 at 2:16 pm
NOT EXISTS evaluates to a boolean (true/false). WHERE NOT EXISTS (Select * FROM tblCapRec)
will still only be true if that table is empty. So, no, it won't work.
August 19, 2004 at 2:20 pm
So what should I put in the WHERE clause then? If a case number already exists in the table, I don't want to insert it. Otherwise, I do want to insert it into the table. WHERE CaseNumber = ???
August 19, 2004 at 2:25 pm
Try:
Not Exists (SELECT * From tblCapRec Where t1.CaseNumber = tblCapRec.CaseNumber)
August 19, 2004 at 3:00 pm
Thanks! It's perfect! Where did you get all of your knowledge?
August 19, 2004 at 3:02 pm
I'm old.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply