title: Merge statement inserting instead of updating in SQL Server
body: <p>I'm using SQL Server 2008 and I'm trying to load a new (target) table from a staging (source) table. The target table is empty.</p>
  
  <p>I think since my target table is empty, the MERGE statement skips the WHEN MATCHED part i.e. result of INNER JOIN is NULL and so nothing is UPDATED, and it just proceed to the WHEN NOT MATCHED BY TARGET part (LEFT OUTER JOIN) an inserts all the records in the staging table.</p>
  
  <p>My target table looks exactly similar to my staging table (rows #1 and #4). There should be only 3 rows in the target table (3 inserts and one update for row #4). So, I'm not sure whats going on.</p>
  
  <pre>
  FileID  client_id account_name  account_currency  creation_date last_modified
  210     12345           Cars            USD       2013-11-21    2013-11-27 
  211     23498           Truck           USD       2013-09-22    2013-11-27 
  212     97652           Cars - 1        USD       2013-09-17    2013-11-27 
  210     12345           Cars            JPY       2013-11-21    2013-11-29 
  </pre>
  
  <p><br />
  <b>QUERY </b></p>
  
  <pre><code>MERGE [AccountSettings] AS tgt -- RIGHT TABLE
  USING
  (
  SELECT * FROM [AccountSettings_Staging]
  ) AS src -- LEFT TABLE
  ON src.client_id = tgt.client_id
  AND src.account_name = tgt.account_name
  WHEN MATCHED -- INNER JOIN
      THEN UPDATE
         SET
           tgt.[FileID] = src.[FileID]
          ,tgt.[account_currency] = src.[account_currency]
          ,tgt.[creation_date] = src.[creation_date]
          ,tgt.[last_modified] = src.[last_modified]
  
  WHEN NOT MATCHED BY TARGET  -- left outer join: A row from the source that has no corresponding row in the target
  THEN INSERT
      (
          [FileID],   
          [client_id], 
          [account_name],
          [account_currency],
          [creation_date], 
          [last_modified] 
      )
      VALUES
      (
          src.[FileID],   
          src.[client_id], 
          src.[account_name],
          src.[account_currency], 
          src.[creation_date], 
          src.[last_modified]             
      );
  </code></pre>