title: Similar WHERE clause in a long UNION statement in SQL Server 2008 R2
body: <p>In a stored procedure, I need to <code>INSERT</code> the result of a long <code>UNION</code> into a temp table.
  The <code>WHERE</code> clause is the same for all tables, which is being in a <code>SELECT DISTINCT</code>.</p>
  
  <p>Simplified for readability, it goes like this:</p>
  
  <pre><code>INSERT INTO #MyTemp
    SELECT col1, col2, col3 FROM tab1 WHERE col1 in (SELECT DISTINCT myId FROM TabIds) UNION
    SELECT col1, col2, col3 FROM tab2 WHERE col1 in (SELECT DISTINCT myId FROM TabIds) UNION
    SELECT col1, col2, col3 FROM tab3 WHERE col1 in (SELECT DISTINCT myId FROM TabIds) UNION
    .
    .
    .
    SELECT col1, col2, col3 FROM tab20 WHERE col1 in (SELECT DISTINCT myId FROM TabIds) 
  </code></pre>
  
  <p>Although <code>TabIds</code> is a small temp table, typically 3-6 records long, this seems to be pretty inneficient.</p>
  
  <p>Is there a better way to do this?</p>
  
  <p><em><strong>Summarizing my question:</em></strong></p>
  
  <p>Is there a way I can do <code>SELECT DISTINCT myId FROM TabIds</code> just once and assign it to a kind of array/list/set (not to another temp table) and just use that in the <code>WHERE</code> clauses, and if there is a way, does it really matter for such a small (3-6 recs) temp table?</p>