CuppM CuppM - 3 years ago 94
SQL Question

SQL Insert Into Temp Table in both If and Else Blocks

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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download