SQL Server conditional temp table definition

#sqlserver

When trying to insert into/create a temporary table in two different ways depending on the state of a variable using IF statements, you are going to run into trouble unless you know this trick.

WON'T WORK!

DECLARE @var INT
SET @var = 2

IF @var = 2
    SELECT *
    INTO #tmpTbl
    FROM sourceTable
    WHERE someVal = 1
ELSE    
    SELECT *
    INTO #tmpTbl
    FROM sourceTable
    WHERE someVal = 2

select * from #tmpTbl

Conditional logic in Sql Server can be annoying because the SQL parser doesn't parse through the logic and recognize you are not actually defining a temporary table twice.

Instead we have to do this hacky first line where the table gets defined but not populated. Here the SELECT * into creates the table but the nonsensical 1=2 makes it so no lines are inserted.

select * into #tmpTbl FROM sourceTable WHERE 1 = 2  --defining table
DECLARE @var INT
SET @var = 2

IF @var = 2
    INSERT INTO #tmpTbl
    SELECT *
    FROM sourceTable
    WHERE someVal = 1
ELSE    
    INSERT INTO #tmpTbl
    SELECT *
    FROM sourceTable
    WHERE someVal = 2

select * from #tmpTbl

Now when we use INSERT INTO there is a created table with no rows that we can now use with our conditional inserts.

The other approach is to use dynamic SQL which is given in this stack overflow answer. In general I try to avoid dynamic sql if at all possible.

https://stackoverflow.com/questions/9573639/t-sql-insert-into-table-without-having-to-specify-every-column

Hope this helps! I need to do a more thorough posting of all the nuances of stored procedures, conditional logic, functions, and dynamic sql. If there is anything specifically confusing in that list this let me know and that may focus my blog efforts.