SQL Insert Into Temp Table in both If and Else Blocks

CuppM picture CuppM · Nov 11, 2010 · Viewed 37.4k times · Source

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

Answer

Gaspa79 picture Gaspa79 · Feb 15, 2018

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, quick, and it works. No dynamic sql needed