title: Microsoft SQL Server - best way to 'Update if exists, or Insert'
body: <p>I've been searching around for the answers to this question, and there's some conflicting or ambiguous information out there, finding it hard to find a for-sure answer.</p>
  <p>My context: I'm in node.js using the 'mssql' npm package. My SQL server is Microsoft SQL Server 2014.</p>
  <p>I have a record that may or may not exist in a table already -- if it exists I want to update it, otherwise I want to insert it. I'm not sure what the optimal SQL is, or if there's some kind of 'transaction' I should be running in mssql. I've found some options that seem good, but I'm not sure about any of them:</p>
  <p><strong>Option 1:</strong>
  <a href="https://stackoverflow.com/questions/42604132/how-to-update-if-exists-or-insert">how to update if exists or insert</a></p>
  <p>Problem with this is I'm not even sure this is valid syntax in MSSQL. I do like it though, and it seems to support doing multiple rows at once too which I like.</p>
  <pre><code>INSERT INTO table (id, user, date, points)
      VALUES (1, 1, '2017-03-03', 25),
             (2, 1, '2017-03-04', 25),
             (3, 2, '2017-03-03', 100),
             (4, 2, '2017-03-04', 150)
      ON DUPLICATE KEY UPDATE points = VALUES(points)
  </code></pre>
  <hr />
  <p><strong>Option 2:</strong></p>
  <p>don't know if there's any problem with this one, just not sure if it's optimal. Doesn't seem to support multiple simultaneous rows</p>
  <pre><code>update test set name='john' where id=3012
  IF @@ROWCOUNT=0
     insert into test(name) values('john');
  </code></pre>
  <hr />
  <p><strong>Option 3:</strong> Merge, <a href="https://dba.stackexchange.com/questions/89696/how-to-insert-or-update-using-single-query">https://dba.stackexchange.com/questions/89696/how-to-insert-or-update-using-single-query</a></p>
  <p>Some people say this is a bit buggy or something? This also apparently supports multiple at once which I like.</p>
  <pre><code>MERGE dbo.Test WITH (SERIALIZABLE) AS T
  USING (VALUES (3012, 'john')) AS U (id, name)
      ON U.id = T.id
  WHEN MATCHED THEN 
      UPDATE SET T.name = U.name
  WHEN NOT MATCHED THEN
      INSERT (id, name) 
      VALUES (U.id, U.name);
  </code></pre>