title: In Sql Server 2008, can I INSERT multiple rows with some fixed column values and some from a SELECT statement that uses one of the fixed values?
body: <p>I’m building an insert statement dynamically to add multiple rows of data to a table in a batch, as I believe it is more efficient than inserting one at a time.  However, I need the last couple of columns in each inserted row to be set with the results of querying another table using a value from the new row.  This is my imaginary pseudocode version:</p>
  <pre><code>INSERT INTO TableA (column1, column2, column3, column4, column5)
  VALUES (SELECT {value1a}, {value1b}, {value1c}, b.column1, b.column2 FROM TableB b WHERE b.column3 = {value1c}),
         (SELECT {value2a}, {value2b}, {value2c}, b.column1, b.column2 FROM TableB b WHERE b.column3 = {value2c}),
          …
  </code></pre>
  <p>Now here is another wrinkle: I have a unique index on TableA with an ignore clause, and there is a lot of redundant data to process, so only about 15% of the rows in any given batch insert will actually be added to the database.  Does this mean it would be more efficient to insert the rows with values for columns 1 – 3, then query for the rows that were inserted, and update column 4 and 5?  If so, would the following be the most efficient way to do that for all the inserted rows?</p>
  <pre><code>UPDATE a SET a.column4 = b.column1, a.column5 = b.column2 
  FROM TableA a INNER JOIN TableB b ON b.column3 = a.column3 
  WHERE a.CreatedAt &gt;= {BatchInsertTime}
  </code></pre>
  <p>(assuming no other processes are adding rows to the table)</p>