How to choose how to fill #temptable in sql?

Question

Okay, I've got what is probably a very easy question for you Sql gurus out there...

Given a boolean of some kind, T, I want to populate a temp table with data set A, if T is true, or data set B, if T is false. I thought this would be how to do it:

DECLARE @foo INT
SET @foo = null

IF (@foo is null)
    BEGIN
        SELECT 'foo was null' INTO #TempTable
    END
ELSE
    BEGIN
        SELECT 'foo not null' INTO #TempTable
    END

But this doesn't work. I get an error that reads "There is already an object named '#TempTable' in the database."

Apparently Sql is trying to create the table in each case, before it evaluates the condition...? That seems odd to me, but okay... But what's the proper way to do something like this?

Edit: I'm in Sql Server, but I suspect that this is a more universal issue...but if I'm wrong, please let me know.

Answer 1

You'll need to create your temporary table first (it should be done once):

CREATE TABLE #TempTable TABLE (
        message VARCHAR(50)
)

, then issue:

DECLARE @foo INT
SET @foo = null

IF (@foo is null)
    BEGIN
        INSERT
        INTO    #TempTable
        SELECT 'foo was null'
    END
ELSE
    BEGIN
        INSERT
        INTO    #TempTable
        SELECT 'foo not null'
    END

Probably you want not a TEMPORARY TABLE, but a TABLE VARIABLE (which lives only during the session and does not persist in the database schema):

DECLARE @foo INT
DECLARE @TempTable TABLE (
        message VARCHAR(50)
        )
SET @foo = null

IF (@foo is null)
    BEGIN
        INSERT
        INTO    @TempTable
        SELECT 'foo was null'
    END
ELSE
    BEGIN
        INSERT
        INTO    @TempTable
        SELECT 'foo not null'
    END

Answer 2

The table has been created already, you'll have to drop it first and then insert into it again.

EDIT: As you said, its not an issue of table being already present in the database, you can try to do it like this -

IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'#TempTable')
    DROP TABLE #TempTable


CREATE TABLE #TempTable (Col1 INT, ...)

IF TRUE
BEGIN
    INSERT INTO #TempTable
    SELECT VAL1
END
ELSE
BEGIN
    INSERT INTO #TempTable
    SELECT VAL2
END

Answer 3

See if this helps...link

You have to create your temp table outside of your select statements and then insert into it. For example,

CREATE TABLE #tmp_table AS SELECT * FROM PRODUCTS WHERE 1 = 2

IF @condition = 0 BEGIN INSERT #tmp_table SELECT * FROM products .... END ELSE ....

Answer 4

SQL is running into this issue because the parsing step is noticing that there are two SELECT INTO statements. Even though your logic would dictate that the temporary table would only be created once, the parser still complains about it.

One way to get around this is by using GO statements after the SELECT INTOs, but that won't work with the conditional logic.

Otherwise, just create the table seperately and insert into it like so:

CREATE TABLE #TempTable (result varchar(50))

IF @foo is null
BEGIN
   insert into #TempTable values ('foo was null')
END
ELSE
BEGIN
   insert into #TempTable values('foo was not null')
END

Answer 5

Why not use a set-based, rather than procedural solution:

SELECT CASE WHEN @foo is null 
            THEN 'foo was null'
            ELSE 'foo not null'
       END                      AS result
INTO  #TempTable

Answer 6

Read What is deferred name resolution and why do you need to care?