Sql Insert Into Temp Table In Both If And Else Blocks

April 20, 2024 Post a Comment

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

Solution 1:

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

select*into #MyTempTable from...
where1=2

IF -- CONDITION HEREinsertinto #MyTempTable select...
ELSEinsertinto #MyTempTable select...

Simple, quick, and it works. No dynamic sql needed

Solution 2:

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@Commandvarchar(500)

DECLARE@IdintSET@Id=2

IF OBJECT_ID('tempdb..#MyTestTable') ISNOTNULLDROPTABLE #MyTestTable 

IF (@Id=2) BEGINSET@Command='SELECT ''ABC'' AS Letters INTO #MyTestTable'ENDELSEBEGINSET@Command='SELECT ''XYZ'' AS Letters INTO #MyTestTable'ENDEXECUTE (@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.

Solution 3:

In the scenario you provide you could do this

DECLARE@IdintSET@Id=1

IF OBJECT_ID('tempdb..#MyTestTable') ISNOTNULLDROPTABLE #MyTestTable

SELECTCASEWHEN (@Id=2) 
    THEN'ABC'ELSE'XYZ'ENDAS Letters
INTO #MyTestTable;

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

CreateTable #MyTestTable (
  MyValue varchar(3)
)
IF (@Id=2) BEGINInsertInto (MyValue)
  SELECT'ABC'AS Letters;
ENDELSEBEGINInsertInto (MyValue)
  SELECT'XYZ'AS Letters;
END

Solution 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 conditionBEGINSELECT*INTO #tmp1 FROM dbo.Table1
ENDELSEBEGINSELECT*INTO #tmp2 FROM dbo.Table2
END-- Inserting data into global temp table so sql server can't complain on not recognizing in a contextDECLARE@CommandVARCHAR(MAX)
IF OBJECT_ID('tempdb..#tmp1') ISNOTNULLBEGINSET@Command='SELECT * INTO ##tmp FROM #tmp1'ENDELSEBEGINSET@Command='SELECT * INTO ##tmp FROM #tmp2'ENDEXECUTE(@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') ISNOTNULLDROPTABLE ##tmp
IF OBJECT_ID('tempdb..#tmp1') ISNOTNULLDROPTABLE #tmp1
IF OBJECT_ID('tempdb..#tmp2') ISNOTNULLDROPTABLE #tmp2

SELECT*FROM #tmpFinal

IF OBJECT_ID('tempdb..#tmpFinal') ISNOTNULLDROPTABLE #tmpFinal

Solution 5:

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@Commandvarchar(500)

DECLARE@IdintSET@Id=2

IF OBJECT_ID('tempdb..#MyTestTable') ISNOTNULLDROPTABLE ##MyTestTable 

IF (@Id=2) BEGINSET@Command='SELECT ''ABC'' AS Letters INTO ##MyTestTable'ENDELSEBEGINSET@Command='SELECT ''XYZ'' AS Letters INTO ##MyTestTable'ENDEXECUTE (@Command)

select*from ##MyTestTable

DROP ##MyTestTable

Lab Sql