title: SELECT INTO #temp.. FROM [Query1] OR [Query2]
body: <p><strong>Added clarification</strong>: My code is run inside a Stored Procedure.</p>
  
  <p>I've run into this issue multiple times over the years so thought I'd bite the bullet and finally ask, even though I think I might be missing a simple solution.</p>
  
  <p>Oftentimes is the case I like to use </p>
  
  <pre><code>SELECT * INTO #Temp FROM ...
  </code></pre>
  
  <p>.. Because I find it generally performs better than any other means of temp table generation when used on small data sets.</p>
  
  <p>But sometimes I'll have a parameterised query which can run in more than one "mode", thus need my statement to be able to populate #Temp from two completely different sources depending on this mode, e.g.</p>
  
  <p>Option 1</p>
  
  <pre><code>IF @RoleID &gt; -1
      SELECT PrivilegeID INTO dbo.#Privs FROM Users.tblRolePrivilegeLink WHERE RoleID = @RoleID
  </code></pre>
  
  <p>Option 2</p>
  
  <pre><code>    IF @UserID &gt; -1 
          SELECT PrivilegeID INTO dbo.#Privs FROM dbo.fn_UserPrivileges(@UserID)
  </code></pre>
  
  <p>In this example I'm trying to get a set of privileges for either a role or a user, one straight from a table, another from a function.</p>
  
  <p>I know I could use the regular INSERT INTO.. logic but I'd be really interested to see if there's an actual solution for doing it this way. Maybe with CTE's or somesuch.</p>
  
  <p><strong>Solution</strong></p>
  
  <pre><code>-- Get distinct privileges
  SELECT  PrivilegeID
  INTO    dbo.#Privs 
  FROM
  (
          SELECT PrivilegeID FROM Users.tblRolePrivilegeLink WHERE RoleID &gt; -1 AND RoleID = @RoleID
          UNION
          SELECT PrivilegeID FROM dbo.fn_UserPrivileges(@UserID) WHERE @UserID &gt; -1
  ) AS T
  </code></pre>