SQL INSERT with LEFT JOIN on Multiple Columns

[mrpatak]

I've been bashing my head against this one for a while, but can't seem to figure out why this one doesn't quite work as expected. The function would be to take an imported set of data in a table (Sheet1) that has been cleaned for erroneous data, check it against the existing data and import any new rows that exist into the main table (MMMainData). Unfortunately, when two of the twelve (ugh) imported columns that make an entry unique are added (Contributing Factors and Special Issue Type), the query additionally inserts any records that have a value of null in either of the two fields. The other ten seem to work as intended, but as soon as either of the other two are added, duplicate entries pop up.
Unfortunately, there is no unique identifier in the files to be imported, as almost everything can be duplicated, the only way to determine if the new record is distinct is on the twelve listed in the join.

INSERT INTO MMMainData ( medrecnum, accountnum, admdate, createdate, discdate, assndate, revtype, specissuetype, servofnote, unitofnote, attndgofnote, revdate, outcm, revservice, mgmt, prevent, actn, contribfactor, revnursing, revphysician, revmemo, servissues, hospacqinfcode, autopsyrequest, orgnsm, daterefsent, refservice, issuecode, nyportsincidid, servrevreason, nyportscode, nyportslocation, nyportsservice, nyportlongformcode, nyportsecndcode, nyportsdx, nyportsproc, occurdate, readmdate, caseinfr, subdate, 30dayreadm, riskofmort, dayssincedisc, nyportssum, lastdc )
SELECT s.[MR#], s.[Acct#], s.AdDate, s.[Create Date], s.DcDate, s.[Date Assigned], s.[Review Type], s.[Special Issue Type], s.[Service of Note], s.[Unit of Note], s.[Attending of Note:], s.[Review Date], s.Outcome, s.[Review Service], s.Management, s.Preventability, s.Action, s.[Contributing Factors], s.[Nursing Reviewer], s.[Review Physician], s.[Reviewer Memo], s.[Service Issues], s.[Hosp Acq Inf Code], s.[Autopsy Requested], s.Organism, s.[Date Referral Sent], s.[Referring Service], s.[Issue Code], s.[NYPORTS Incident ID], s.[Service Rev Reason], s.[NYPORTS Code], s.[NYPORTS Location], s.[Nyports Service], s.[NYPORT LongForm Cd], s.[NYPORT Secondary Cod], s.[Nyports Dx Code], s.[NYPORTS Procedure], s.[Occurrence Date], s.[Readmission Date], s.[Case Information], s.[Submission Date], s.[30 Day Readm], s.ROM, s.DaysSinceDC, s.[NYPORTS Summary], s.[LastD/C]
FROM 
  Sheet1 as s
  LEFT JOIN MMMainData as m ON
	            (m.medrecnum =s.[MR#]) and
		    (m.accountnum =s.[Acct#]) and
                    (m.admdate = s.[AdDate]) and
                    (m.createdate = s.[Create Date]) and
                    (m.discdate = s.[DcDate]) and
                    (m.assndate = s.[Date Assigned]) and
                    (m.revtype = s.[Review Type]) and
                    (m.specissuetype = s.[Special Issue Type]) and
                    (m.servofnote = s.[Service of Note]) and
                    (m.unitofnote = s.[Unit of Note]) and
                    (m.attndgofnote = s.[Attending of Note:]) and
                    (m.contribfactor = s.[Contributing Factors])
WHERE m.medrecnum is Null;

 

[genomon]

What happens when you use SELECT DISTINCT... ?

Beir bua agus beannacht!