title: CTE vs multiple inserts
body: <p>As we all know, CTEs were introduced in SQL Server 2005 and the crowd went wild.</p>
  
  <p>I have a case where I'm inserting a whole lot of static data into a table. What I want to know is which of the following is faster and what other factors I should be aware of.</p>
  
  <pre><code>INSERT INTO MyTable (MyField) VALUES ('Hello')
  INSERT INTO MyTable (MyField) VALUES ('World')
  </code></pre>
  
  <p>or</p>
  
  <pre><code>WITH MyCTE(Field1) AS (SELECT 'Hello' UNION SELECT 'World')
  INSERT INTO MyTable (MyField) SELECT Field1 FROM MyCTE
  </code></pre>
  
  <p>I have an uncomfortable feeling the answer will be dependent on things like what triggers exist on <code>MyTable</code>...</p>
  
  <p>(Also, I know and don't care that <code>BULK INSERT</code>ing a CSV and any number of other methods are objectively faster and better ways of inserting static data. I specifically want to know the concerns I should be aware of with a CTE vs multiple inserts.)</p>