title: Complex TSQL Merge
body: <p>I have '<em>inherited</em>' a brilliant piece of TSQL code that does this:</p>
  
  <ul>
  <li>Loops row-by-row over a cursor.</li>
  <li>The cursor contains data that need to be merged (Upserted) in Table A</li>
  <li>For each row loop in the cursor a stored proc is called. The proc:
  <ul>
  <li>If a corresponding row exists in Table A then it is updated</li>
  <li>If such a row does not exist then:
  <ul>
  <li>Inserts a single row in in a different Table B. </li>
  <li>Fetches the newly generated ID (say its called IDB)</li>
  <li>Inserts a single row in Table A. Table A insertions <em>need</em> an IDB (the field is not null, it is supposed to have values ONLY from table B, but no FK constraint is in place)</li>
  </ul></li>
  </ul></li>
  </ul>
  
  <p>Obviously this sucks (performance &amp; elegance reasons)!!</p>
  
  <p><strong>Question</strong>
  At first this looks like a standard case of MERGE usage. I tried doing:</p>
  
  <pre><code>MERGE [dbo].[TableA] AS Target
  USING &lt;cursor data set as a select statement&gt; as Src on target.IDA = Src.IDA
  WHEN MATCHED 
    //update
  WHEN NOT MATCHED
  //insert &lt;------ Fails because obviously a new IDB is required
  </code></pre>
  
  <p>Also tried various approaches like a <code>nested select that sends IDB on the OUTPUT</code> but it fails because IDB is a PK.</p>
  
  <p>Other kinds of merges also failed eg:</p>
  
  <pre><code>MERGE Table A with &lt;cursor data set as a select statement&gt;
  ...
  MERGE Table A with Table B
  WHEN NOT MATCHED
  //insert on Table A
  WHEN NOT MATCHED
  // Update Table B
  </code></pre>
  
  <p>Does anyone have an idea on this? Essentially I think if we generalise the question would be:</p>
  
  <p><code>Can I insert and return the PK in one statement that can be nested in other statements</code></p>
  
  <p>Thanks in advance for any replies</p>
  
  <p>George</p>