How to check temp table exists while Union multiple temp tables?

here my query-

SELECT final.* into #FinalTemp from
    (   
    select * from #temp1 
        UNION 
    select * from #temp2 
        UNION 
    select * from #temp3 
        UNION 
    select * from #temp4
)final

but at a开发者_开发技巧 time only one temp table exists so how to check if #temp exists then do union or ignore?

---

You can't have a union or query on a non-existent object at compile time (compiling to a query plan just before execution).

So there is no way to refer to a non-existent table in the same batch

The pattern you have to use is like this: dynamic SQL is a separate batch

IF OBJECT('tempdb..#temp1') IS NOT NULL
  EXEC ('SELECT * FROM #temp1')
ELSE IF OBJECT('tempdb..#temp3') IS NOT NULL
  EXEC ('SELECT * FROM #temp3')
ELSE IF OBJECT('tempdb..#temp3') IS NOT NULL
  EXEC ('SELECT * FROM #temp3')
...

---

Would you not be better creating #FinalTemp as an explicit temp table at the top of your query, and then replace your existing population methods which I assume look like this:

SELECT * INTO #temp1 FROM ... /* Rest of Query */

With:

INSERT INTO #FinalTemp (Columns...)
SELECT * FROM ... /* Rest of Query */

And then you don't have to do this final union step at all. Or, if you do need 4 separate temp tables (perhaps for multi-step operations on each), define each of them at the start of your query, and then they will all exist when you perform the union.

Now, given you've said only one will be populated (so the others will be empty), it's probably moot, but I always tend to use UNION ALL to combine disjoint tables - unless you're implicitly relying on UNIONs duplicate removal feature?

---

You can declare Temp Tables using the same syntax as you do for real tables:

CREATE TABLE #FinalTemp (
    ColumnA int not null primary key,
    ColumnB varchar(20) not null,
    ColumnC decimal(19,5) null,
)

Or, as you've also alluded to, you can use table variables rather than temp tables:

declare @FinalTemp table (
    ColumnA int not null primary key,
    ColumnB varchar(20) not null,
    ColumnC decimal(19,5) null,
)

The predominant different (so far as I'm concerned) is that table variables follow the same scoping rules as other variables - they're not available inside a called stored procedure, and they're cleaned up between batches.