By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,528 Members | 1,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,528 IT Pros & Developers. It's quick & easy.

Insert records, using SQL, only if record does not exist

P: 2
Here's my SQL:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO HFIAAREF                                                  
  2. (Select Distinct                                                    PolSt, PolNo, PolCn, PolYr, PstDa@, 0, 0, 0, 0, Compny, AgHd, AgBr, AgDv, AgID, 0, 0, 0, BlRatB, AlRatB, BlRatC,AlRatC, ' ', ' ', ' ', '     ', 'HFIAAPF12 ', 'SUSMST    ',Current_Timestamp, User, RegEmr, DckDa@, Status, RateSw, Condo, FloodZ, ZoneNo, OccCod, NumFlr, AlAmtB, BasTyp,EleDif, CovReq, TBAI, TCAI, BsFldE, RskRat, PRPRskRat,Elev, Coalesce(PsbMeEncl, ''), Coalesce(PsbVCrawl, ''),ObsInd, InsInd, OrCo@, NewCst, CStID, CComID,Coalesce(CmFrCC, 0), Coalesce(CmFrCY, 0), Coalesce(CmFrCM, 0),Coalesce(CmFrCD, 0), Fil1Date, SUBSTRING(FIL2ALPH,9,1)        
  3. FROM SUSMST                                                           
  4. JOIN PPOLADDL on (PolSt = PolPolSt and PolNo = PolPolNo and PolCn = PolPolCn and PolYr = PolPolYr)              
  5. LEFT OUTER JOIN PSUBMIT on (PolSt = PsbPolSt and PolNo = PsbPolNo and PolCn = PsbPolCn and PolYr = PsbPolYr)    
  6. LEFT OUTER JOIN CUMST on (CStID = CmSt# and CComID = CmID#)           
  7. WHERE PstDa@ >= 20131001 and DckDa@ <> 0 and ((Fil1Date > '2012-07-06') or (substring(FIL2ALPH,9,1) = 'Y')));
My issue is I do not want to insert the record into HFIAAREF if POLST, POLNO, POLCN and POLYR already exist in HFIAAREF. Any ideas?
Jul 23 '14 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,430
Please use code tags when posting code or formatted data.

Create a unique index on those fields and that will prevent someone from inserting duplicates.
Jul 23 '14 #2

P: 2
@Rabbit
Will that not cause the insert to error when the duplicate is attempted? Please excuse my ignorance as this is a bit more complex SQL than I'm used to writing
Jul 24 '14 #3

Rabbit
Expert Mod 10K+
P: 12,430
Yes, it throws an error. As it should, it's the only 100% way of knowing you can never insert a duplicate.
Jul 24 '14 #4

Expert 100+
P: 1,043
Google found the answer on how to do a 'INSERT IGNORE' (MySQL code) in MSSQL

the link is here
Jul 24 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.