Exists Function?

  • 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

  • "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.

  • Where do I put the "'WHERE NOT EXISTS"?  Can you show me?  Thanks!

  • <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 .

  • 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?

  • 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.

  • 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 = ???

  • Try:

    Not Exists (SELECT * From tblCapRec Where t1.CaseNumber = tblCapRec.CaseNumber)

  • Thanks!  It's perfect!  Where did you get all of your knowledge?

  • 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