title: SQL Server: Insert batch with output clause
body: <p>I'm trying the following</p>
  
  <ol>
  <li>Insert number of records to table <code>A</code> with a table-valued parameter (tvp). This tvp has extra column(s) that are not in <code>A</code></li>
  <li>Get the inserted ids from <code>A</code> and the corresponding extra columns in the the tvp and add them to another table <code>B</code></li>
  </ol>
  
  <p>Here's what I tried</p>
  
  <p>Type:</p>
  
  <pre><code>CREATE TYPE tvp AS TABLE
  (
      id int,
      otherid int,
      name nvarchar(50),
      age int
  );
  </code></pre>
  
  <p>Tables:</p>
  
  <pre><code>CREATE TABLE A (
      [id_A] [int] IDENTITY(1,1) NOT NULL,
      [name] [varchar](50),
      [age] [int]
  );
  
  CREATE TABLE B (
      [id_B] [int] IDENTITY(1,1) NOT NULL,
      [id_A] [int],
      [otherid] [int]
  );
  </code></pre>
  
  <p>Insert:</p>
  
  <pre><code>DECLARE @a1 AS tvp;
  DECLARE @a2 AS tvp
  
  -- create a tvp (dummy data here - will be passed to as a param to an SP)
  INSERT INTO @a1 (name, age, otherid) VALUES ('yy', 10, 99999), ('bb', 20, 88888);
  
  INSERT INTO A (name, age) 
  OUTPUT 
      inserted.id_A, 
      inserted.name, 
      inserted.age, 
      a.otherid -- &lt;== isn't accepted here
  INTO @a2 (id, name, age, otherid)
  SELECT name, age FROM @a1 a;
  
  INSERT INTO B (id_A, otherid) SELECT id, otherid FROM @a2
  </code></pre>
  
  <p>However, this fails with <code>The multi-part identifier "a.otherid" could not be bound.</code>, which I guess is expected because columns from other tables are not accepted for <code>INSERT</code> statement (<a href="https://msdn.microsoft.com/en-au/library/ms177564.aspx" rel="nofollow">https://msdn.microsoft.com/en-au/library/ms177564.aspx</a>).</p>
  
  <blockquote>
    <p>from_table_name
    Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.</p>
  </blockquote>
  
  <p>So is there any other way to achieve this?</p>