title: Insert Results From Dynamic SQL Into 2 Table Variables
body: <p>I need to execute some dynamic SQL that will return 2 result sets and store the result sets in a table variable.</p>
  
  <p>Let's say I have 2 tables (crappy schema but illustrates my issue)</p>
  
  <p>Table1: </p>
  
  <pre><code> ItemID int
   ItemName nvarchar(50)
  </code></pre>
  
  <p>Table2:</p>
  
  <pre><code>ItemId int
  Quantity int
  </code></pre>
  
  <p>I generate some dynamic sql that looks like this:</p>
  
  <pre><code>DECALRE @sql varchar(max);
  SET @sql = 'SELECT * FROM Table1; SELECT * from Table2';
  </code></pre>
  
  <p>Then I create my table variables:</p>
  
  <pre><code>DECALRE @tbl1 TABLE (ItemId int, ItemName nvarchar(50))
  DECALRE @tbl2 TABLE (ItemId int, Quantity int)
  </code></pre>
  
  <p>Then I want to execute that dynamic SQL and insert the results into the table variables I just declared. If there was just one result set in the dynamic sql I could simply just run this:</p>
  
  <pre><code>INSERT into @tbl1execute ('SELECT * FROM Table1;')
  </code></pre>
  
  <p>However, this obviously fails as soon as I use the @sql parameter that will return multiple result sets. Is this even possible?</p>