SQL Insert Into Temp Table in both If and Else Blocks

Question

I'm trying to populate a temp table based on the result of a condition in SQL 2005. The temp table will have the same structure either way, but will be populated using a different query depending on the condition. The simplified example script below fails in syntax checking of the ELSE block INSERT INTO with the error of:

There is already an object named '#MyTestTable' in the database.

DECLARE @Id int
SET @Id = 1

IF OBJECT_ID('tempdb..#MyTestTable') IS NOT NULL DROP TABLE #MyTestTable

IF (@Id = 2) BEGIN 
    SELECT 'ABC' AS Letters
    INTO #MyTestTable;
END ELSE BEGIN
    SELECT 'XYZ' AS Letters
    INTO #MyTestTable;
END

I could create the temp table before the IF/ELSE statement and then just do INSERT SELECT statements in the conditional blocks, but the table will have lots of columns and I was trying to be efficient about it. Is that the only option? Or is there some way to make this work?

Thanks, Matt

Becharm

Answer 1

The problem you’re having is not that you are populating the temp table, but that you’re trying to create the table. SQL parses your script and finds that you are attempting to create it in two different places, and so raises an error. It is not clever enough to realize that the “execution path” cannot possibly hit both of the create statemements. Using dynamic SQL will not work; I tried

DECLARE @Command  varchar(500)

DECLARE @Id int 
SET @Id = 2

IF OBJECT_ID('tempdb..#MyTestTable') IS NOT NULL DROP TABLE #MyTestTable 

IF (@Id = 2) BEGIN  
    SET @Command = 'SELECT ''ABC'' AS Letters INTO #MyTestTable'
END ELSE BEGIN 
    SET @Command = 'SELECT ''XYZ'' AS Letters INTO #MyTestTable'
END 

EXECUTE (@Command)

select * from #MyTestTable

but the temp table only lasts as long as the dynamic session. So, alas, it looks like you’ll have to first declare the table and then populate it. Awkward code to write and support, perhaps, but it will perform efficiently enough.

Dor

My answer neither says to nor recommends the use of dynamic SQL -- in fact, it demonstrates that dynamic SQL cannot be used to solve the problem, and concludes that specificly declaring the temp table before data insertion is probably the way to go. – Dor

You can use dynamic SQL and insert into a global temp table (##MyTesttable) and delete the global table afterwards. Will add as a separate answer – Wicopy

Answer 2

Answering 8 years late, but I'm surprised nobody thought of:

select * into #MyTempTable from...
where 1=2

IF -- CONDITION HERE
insert into #MyTempTable select...
ELSE
insert into #MyTempTable select...

Simple and quick. No dynamic sql needed.

EDIT: As Sushant said in the comments, Select top 0 will also work in T-SQL and it looks a bit 'better' to most. That being said, its performance is the exact same as where 1=2 (verified with query planner) so choose as you please.

Salk

Brilliant! Years later is still fantastic for those of us searching for help. – Lialiabilities

A good solution indeed but would like to add that 'Select Top 0 *' would be better than imposing a condition. Also, if the original table contains an identity column then it will have to be disabled before the insertion. – Unhook

"Where 1=2" performs the exact same as top 0 (and it also works in mysql) so it's not objectively better than imposing a condition. You're right about the ID col though. – Salk

Answer 3

In the scenario you provide you could do this

DECLARE @Id int
SET @Id = 1

IF OBJECT_ID('tempdb..#MyTestTable') IS NOT NULL DROP TABLE #MyTestTable

SELECT 
  CASE WHEN (@Id = 2) 
    THEN 'ABC' 
    ELSE 'XYZ' 
  END AS Letters
INTO #MyTestTable;

But otherwise you will need to create the table before the if statement like this

Create Table #MyTestTable (
  MyValue varchar(3)
)
IF (@Id = 2) BEGIN 
  Insert Into (MyValue)
  SELECT 'ABC' AS Letters;
END ELSE BEGIN
  Insert Into (MyValue)
  SELECT 'XYZ' AS Letters;
END

Miceli

Yes, for this scenario that would work. But the actual queries are much more complex. – Becharm

Answer 4

Here is a solution which I use if temp table can't be created upfront and don't want to put core logic in dynamic SQL.

IF 1 = 1 -- Replace with actual condition
BEGIN
    SELECT * INTO #tmp1 FROM dbo.Table1
END
ELSE
BEGIN
    SELECT * INTO #tmp2 FROM dbo.Table2
END

-- Inserting data into global temp table so sql server can't complain on not recognizing in a context
DECLARE @Command VARCHAR(MAX)
IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL
BEGIN
    SET @Command = 'SELECT * INTO ##tmp FROM #tmp1'
END
ELSE
BEGIN
    SET @Command = 'SELECT * INTO ##tmp FROM #tmp2'
END

EXECUTE(@Command)
SELECT * INTO #tmpFinal FROM ##tmp -- Again passing data back to local temp table from global temp table to avoid seeing red mark

IF OBJECT_ID('tempdb..##tmp') IS NOT NULL DROP TABLE ##tmp
IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL DROP TABLE #tmp1
IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL DROP TABLE #tmp2

SELECT * FROM #tmpFinal

IF OBJECT_ID('tempdb..#tmpFinal') IS NOT NULL DROP TABLE #tmpFinal

Documentary

Answer 5

You could drop the table before SELECTing INTO it in both cases., e.g.:

DECLARE @Id int 
SET @Id = 1  

IF (@Id = 2) BEGIN  
    IF OBJECT_ID('tempdb..#MyTestTable') IS NOT NULL DROP TABLE #MyTestTable
    SELECT 'ABC' AS Letters 
    INTO #MyTestTable; 
END ELSE BEGIN 
    IF OBJECT_ID('tempdb..#MyTestTable') IS NOT NULL DROP TABLE #MyTestTable 
    SELECT 'XYZ' AS Letters 
    INTO #MyTestTable; 
END 

Update After Comment:

That's annoying.

How about two separate temp tables? Then after the If/Else login, check for the existence of each one and if it exists, select into a third temp table? That may not perform great, but whether that matters or not depends on what you need this for.

Tedric

I tried that. I put the IF OBJECT_ID('tempdb..#MyTestTable') IS NOT NULL DROP TABLE #MyTestTable in each block before the INSERT INTO statment, but resulted in the same. – Becharm

Wasn't me. But maybe it was because your suggestion doesn't pass the syntax checking either? I think 2 separate temp tables starts making more work than just creating the temp table before the IF/ELSE. – Becharm

Yep, creating the temp table before the logic seems the best option, but without knowing more about what you're doing, it's hard to say definitively. – Tedric

Answer 6

This is an old issue, but for anyone else coming here:

The dynamic SQL answer given by user Philip Kelley does not work for local temp tables (#Mytemp). What you can do is create dynamic SQL to insert it into a global temp table (##MyTemp) which can later be dropped.

DECLARE @Command  varchar(500)

DECLARE @Id int 
SET @Id = 2

IF OBJECT_ID('tempdb..#MyTestTable') IS NOT NULL DROP TABLE ##MyTestTable 

IF (@Id = 2) BEGIN  
    SET @Command = 'SELECT ''ABC'' AS Letters INTO ##MyTestTable'
END ELSE BEGIN 
    SET @Command = 'SELECT ''XYZ'' AS Letters INTO ##MyTestTable'
END 

EXECUTE (@Command)

select * from ##MyTestTable

DROP ##MyTestTable

Wicopy

Answer 7

I tried this:

SELECT S1.* INTO #MytestTable
FROM 
(   SELECT 'ABC' AS Letters WHERE 1 = CASE @Id=2 THEN 1 ELSE 2 END
    UNION
    SELECT 'XYZ' AS Letters WHERE 1 = CASE @Id=1 THEN 1 ELSE 2 END
) AS S1

This solution is better if later you need to add columns to #MyTestTable, because otherwise you must physically drop it before you re-run your script, which is annoying iin test conditions.

Mauldin

Answer 8

this code may help you

--creating temptable using columns of two existing tables
--you can create your temp table Using other methods

select top 0 VI.*,VU.FullName
into #mytemptable
from dbo.Items VI inner join
     dbo.Users as VU 
     on VU.Id=VI.Id

--insert your data base on your condition
if(i<2) --First Condition
begin
INSERT INTO #mytemptable
SELECT VI.*,VU.FullName 
from dbo.Items VI inner join
     dbo.Users as VU 
     on VU.Id=VI.Id
end
Else if(2<i) --Second Condition
begin
INSERT INTO #mytemptable
SELECT VI.*,VU.FullName 
from dbo.Items VI inner join
     dbo.Users as VU 
     on VU.Id=VI.Id
end

select * from #mytemptable --show result

drop table #mytemptable --drop table if its needed

this code works in sql server 2014 i don't know if it works in sql 2005 or not

Blackpoll

Answer 9

You can try this code.

IF (CONDITION HERE)
       begin
           select * into #MyTempTable from tablename ....
       end
       ELSE
           truncate table #MyTempTable 
           insert into #MyTempTable 
           select * from tablename ....
       end

Thanks!!!

Gothard

That's not at all a solution – Horizontal