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
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
END ELSE BEGIN
SELECT 'XYZ' AS Letters
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.