title: Upsert from select
body: <p>I need to perform an upsert from select like <a href="https://stackoverflow.com/questions/44466232/postgres-upsert-using-results-from-select">this</a> but in SQL Server. I found someone doing an upsert in <a href="https://stackoverflow.com/questions/11010511/how-to-upsert-update-or-insert-in-sql-server-2005">SQL Server here</a>, but that's not from a select. </p>
  
  <p>My query basically looks like this right now:</p>
  
  <pre><code>INSERT INTO (table1) (...)
      SELECT (...)
      FROM (table 2)
      WHERE X NOT IN (SELECT Y from (table1) WHERE Y IS NOT NULL)
  </code></pre>
  
  <p>But I can't figure out how to add the update part. I would like to add the update part after since 99% of the time it will be an insert.</p>
  
  <p>Edit: this code would work if I was able to do it from a <code>SELECT</code>:</p>
  
  <pre><code>BEGIN TRY
      INSERT INTO table1 (id, name, itemname, itemcatName, itemQty)
      VALUES ('val1', 'val2', 'val3', 'val4', 'val5')
  END TRY
  BEGIN CATCH
      UPDATE table1 
      SET name = 'val2', 
          itemname = 'val3', 
          itemcatName = 'val4', 
          itemQty = 'val5'
      WHERE id = 'val1'
  END CATCH
  </code></pre>