title: What is USING in SQL Server 2008 MERGE syntax?
body: <p>Jacob asked the perfect question: <a href="https://stackoverflow.com/questions/2479488/syntax-for-single-row-merge-upsert-in-sql-server"><em>give me the <code>MERGE</code> syntax</em></a>.</p>
  
  <p>Every answer out there immediately jumps to the most complicated case they can think of; obscuring the syntax with extraneous confusion.</p>
  
  <p>Marc gave <a href="https://stackoverflow.com/a/2479522/12597">an answer</a>:</p>
  
  <pre><code>MERGE 
     member_topic AS target
  USING 
     someOtherTable AS source
  ON 
     target.mt_member = source.mt_member 
     AND source.mt_member = 0 
     AND source.mt_topic = 110
  WHEN MATCHED THEN 
     UPDATE SET mt_notes = 'test'
  WHEN NOT MATCHED THEN 
     INSERT (mt_member, mt_topic, mt_notes) VALUES (0, 110, 'test')
  ; 
  </code></pre>
  
  <p>Looking at this answer, i am as confused as Jacob was:</p>
  
  <blockquote>
    <p>I don't have a someOtherTable</p>
  </blockquote>
  
  <p>Marc suggested that <code>someOtherTable</code> is a dummy placeholder value - it doesn't matter that you don't have that table.</p>
  
  <blockquote>
    <p>i try it, and SQL Server <em>does</em> complain</p>
    
    <p>Invalid object name 'someOtherTable'.</p>
  </blockquote>
  
  <p>That leaves me struggling to understand what the <code>USING</code> in <code>USING foo</code> is <em>for</em> if it's not important (except actually important).</p>
  
  <p>What is <code>USING</code> using when it's using <em>foo</em> when i use SQL Server 2008 MERGE syntax?</p>
  
  <hr>
  
  <h2>Bonus Question</h2>
  
  <p>What is the UPSERT syntax using MERGE:</p>
  
  <pre><code>IF (rowExists)
     UPDATE Users SET Firstname='Ian', LastName='Boyd' WHERE Username='iboyd'
  ELSE
     INSERT INTO Users (UserGUID, Username, FirstName, LastName, AuthenticationMethod)
     VALUES ('{77410DC5-7A3E-4F1A-82C6-8EFB3068DE66}', 'iboyd', 'Ian', 'Boyd', 'Windows')
  </code></pre>
  
  <p>becomes (exact code i tried):</p>
  
  <pre><code>begin transaction
  
      MERGE 
         Users
      USING 
         foo
      ON  
         Users.UserName = foo.UserName
      WHEN MATCHED THEN
          UPDATE SET Firstname = foo.FirstName, Lastname = foo.LastName
      WHEN NOT MATCHED THEN
          INSERT (UserGUID, Username, FirstName, LastName, AuthenticationMethod)
          VALUES ('{77410DC5-7A3E-4F1A-82C6-8EFB3068DE66}', 'iboyd', 'Ian', 'Boyd', 'Windows')
      ; --A MERGE statement must be terminated by a semi-colon (;).
  
  rollback
  
  Msg 208, Level 16, State 1, Line 3
  Invalid object name 'foo'.
  </code></pre>
  
  <p>?</p>
  
  <blockquote>
    <p>With a <code>Users</code> table that contains the columns:</p>
  
  <pre><code>UserGUID uniqueidentifier
  Username varchar(50)
  FirstName varchar(50)
  LastName varchar(50)
  AuthenticationMethod varchar(50)
  </code></pre>
  </blockquote>
  
  <hr>
  
  <p><strong>Update:</strong></p>
  
  <pre><code>USING &lt;table_source&gt; 
  </code></pre>
  
  <p>Where <code>table_source</code> is:</p>
  
  <pre><code>table_or_view_name [ [ AS ] table_alias ] [ &lt;tablesample_clause&gt; ] 
      [ WITH ( table_hint [ [ , ]...n ] ) ] 
  | rowset_function [ [ AS ] table_alias ] 
      [ ( bulk_column_alias [ ,...n ] ) ] 
  | user_defined_function [ [ AS ] table_alias ]
  | OPENXML &lt;openxml_clause&gt; 
  | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
  | &lt;joined_table&gt; 
  | &lt;pivoted_table&gt; 
  | &lt;unpivoted_table&gt; 
  </code></pre>
  
  <p>Where <code>joined_table</code> is:</p>
  
  <blockquote>
    <p><em>undefined</em></p>
  </blockquote>
  
  <p>Where <code>pivoted_table</code> is:</p>
  
  <blockquote>
    <p><em>undefined</em></p>
  </blockquote>
  
  <p>Where <code>unpivoted_table</code> is:</p>
  
  <blockquote>
    <p><em>undefined</em></p>
  </blockquote>