DEV Community

theBridge2
theBridge2

Posted on • Edited on

SQL Server conditional temp table definition

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
Enter fullscreen mode Exit fullscreen mode

Image description

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
Enter fullscreen mode Exit fullscreen mode

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.

Image of Datadog

How to Diagram Your Cloud Architecture

Cloud architecture diagrams provide critical visibility into the resources in your environment and how they’re connected. In our latest eBook, AWS Solution Architects Jason Mimick and James Wenzel walk through best practices on how to build effective and professional diagrams.

Download the Free eBook

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more