SQL Insert Into Temp Table in If and Else Blocks

Question

I am trying to populate a temporary table based on the result of a condition in SQL 2005. The temporary table will have the same structure anyway, but will be populated using a different query depending on the condition. The following is a simplified script example in parsing an ELSE INSERT INTO block with an error:

An object already exists with the name '#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 can create a temporary table before the IF/ELSE and then just make the INSERT SELECT in conditional blocks, but there will be many columns in the table, and I tried to be efficient. Is this the only option? Or is there a way to make this work?

Thanks Matt

Answer 1

The problem you are facing is not that you are filling out a temporary table, but that you are trying to create a table. SQL parses your script and finds that you are trying to create it in two different places, and therefore causes an error. It is not smart enough to understand that the "path of execution" cannot hit both created 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 lasts as long as the dynamic session. So alas, it seems you will have to declare the table first and then populate it. Awkward code for writing and supporting, perhaps, but it will work quite efficiently.

Answer 2

Answering 8 years late, but I am surprised that no one thought:

 select * into #MyTempTable from... where 1=2 IF -- CONDITION HERE insert into #MyTempTable select... ELSE insert into #MyTempTable select... 

Simple, fast, and it works. Dynamic sql not required

Answer 3

In the scenario you proposed, you can 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 a table before the if statement as follows

 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 

Answer 4

Here is the solution I use if the temporary table cannot be created in advance and does not want to put the main 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 

Answer 5

This is an old problem, but for everyone who comes here:

The dynamic SQL response defined by the Philip Kelley user does not work for local temporary tables ( #Mytemp ). You can create dynamic SQL to insert it into the global temp table ( ##MyTemp ), which can later be deleted.

 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 

Answer 6

this code can 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

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 will be better if you later need to add columns to #MyTestTable, because otherwise you must physically drop it before re-running the script, which is annoying in testing conditions.

Answer 8

You can delete a table before selecting it in both cases, for example:

 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:

This is annoying.

What about two separate temporary tables? Then, after logging in to If / Else, check for each one, and if it exists, select a third temporary table? It may not be very good, but whether it depends on what you need for this.