title: Upsert - SQL Server 2005
body: <p>I have two tables: </p>
  
  <ol>
  <li><code>NewNotes</code></li>
  <li><code>MasterNotes</code></li>
  </ol>
  
  <p>Every month the <code>NewNotes</code> table is updated with new notes for both new and existing accounts. I need to either update existing notes in the <code>MasterNotes</code> table or insert new notes if the account number isn't yet in the <code>MasterNotes</code> table.  </p>
  
  <p>Both tables have three columns: <code>REPORT</code>, <code>ACCOUNT_NUMBER</code>, <code>NOTES</code>.  </p>
  
  <p>To update a record both the account number and report have to be the same (the <code>MasterNotes</code> table can have two different account numbers with two different reports). </p>
  
  <p>Can anybody help with this?  </p>
  
  <p>Here's my thinking thus far:</p>
  
  <pre><code>IF NOT EXISTS
  (
  SELECT REP, ACCOUNT_NUMBER
  FROM MasterNotes
  WHERE REP = (SELECT Report FROM NewNotes) AND
        ACCOUNT_NUMBER = (SELECT AccountNo FROM NewNotes)
  )
  INSERT INTO MasterNotes
  ELSE
  UPDATE
  </code></pre>
  
  <p>I know that's not correct because the subquery returns more than one value, but I'm note sure how to go about it row by row.</p>
  
  <p>Or maybe something like this?</p>
  
  <pre><code>INSERT INTO MasterNotes
  SELECT Report, AccountNo, A.Notes
  FROM NewNotes A 
  LEFT OUTER JOIN MasterNotes B
      ON A.Report = B.REP AND A.AccountNo = B.ACCOUNT_NUMBER
  WHERE B.ACCOUNT_NUMBER IS NULL AND B.REP IS NULL
  UPDATE MasterNotes
  SET NOTE = B.Notes
  FROM MasterNotes A, NewNotes B
  WHERE A.ACCOUNT_NUMBER = B.AccountNo AND A.REP = B.Report
  </code></pre>