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
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