SQL Insert Into Temp Table in both If and Else Blocks

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

select * into #MyTempTable from...where 1=2IF -- CONDITION HEREinsert into #MyTempTable select...ELSEinsert into #MyTempTable select...

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

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 = 2IF 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.

In the scenario you provide you could do this

DECLARE @Id intSET @Id = 1IF OBJECT_ID('tempdb..#MyTestTable') IS NOT NULL DROP TABLE #MyTestTableSELECT   CASE WHEN (@Id = 2)     THEN 'ABC'     ELSE 'XYZ'   END AS LettersINTO #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