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

[narace9152]

Here's my SQL:

INSERT INTO HFIAAREF                                                  
(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)        
FROM SUSMST                                                           
JOIN PPOLADDL on (PolSt = PolPolSt and PolNo = PolPolNo and PolCn = PolPolCn and PolYr = PolPolYr)              
LEFT OUTER JOIN PSUBMIT on (PolSt = PsbPolSt and PolNo = PsbPolNo and PolCn = PsbPolCn and PolYr = PsbPolYr)    
LEFT OUTER JOIN CUMST on (CStID = CmSt# and CComID = CmID#)           
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?

[Rabbit]

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.

[narace9152]

Does unique key cause SQL error

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.

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

[Rabbit]

Yes, it throws an error. As it should, it's the only 100% way of knowing you can never insert a duplicate.

[Luuk]

Google found the answer on how to do a 'INSERT IGNORE' (MySQL code) in MSSQL

the link is here