How does this piece of TSQL work?

Why the following batch doesn't work as expected?

DROP TABLE IF EXISTS #test1;

IF 1 = 1
    SELECT *
    INTO #test1
    FROM sys.databases
    WHERE database_id <= 5;

ELSE
    SELECT *
    INTO #test1
    FROM sys.databases;

SELECT *
FROM #test1;

When I run it, I got:

Msg 2714, Level 16, State 1, Line 12
There is already an object named '#test1' in the database.

And I have to change the code like this:

DROP TABLE IF EXISTS ##test1;

IF 1 = 1
    EXEC ('
    SELECT *
    INTO ##test1
    FROM sys.databases
    WHERE database_id <= 5;
    '    );

ELSE
    EXEC ('
    SELECT *
    INTO ##test1
    FROM sys.databases;');

SELECT *
FROM ##test1;

This works as expected. The aforementioned error seems like a parsing error, which I'm not sure. Could anyone help to explain what's happening here?

The error is generated at parse time, so before the batch is even executed. And it only happens when the INTO target is a temporary table (either local or global). It does not occur when the target is a regular table.

One way to work around this issue is to use dynamic SQL, as you have shown. And as you have also discovered, the method does not work with local temporary tables, which may not always be fine.

You can also create the target table beforehand and switch to populating it with INSERT...SELECT instead of SELECT...INTO, as suggested by Aleksey Vitsko. One situation I can see this might not be convenient is when the query returns multiple calculated columns, as you would first have to determine the data types correctly in order to accommodate the output data without causing overflow, precision loss, conversion errors or other such things.

To address both issues (keep using local temporary tables, avoid having to define target columns explicitly), you could try rewriting the script like this:

DROP TABLE IF EXISTS #test1;

/* the query from the "if true" branch */
SELECT
  *
INTO
  #test1
FROM
  sys.databases
WHERE
  database_id <= 5
  AND ...  /* your IF condition should go here */
;

IF NOT (...)  /* the negated version of your IF condition goes here;
                 make sure to account for possible "unknowns" */
  /* the "else" branch query */
  INSERT INTO
    #test1
  SELECT
    *
  FROM
    sys.databases
;

SELECT * FROM #test1;

The first query will run and create the target table. If the IF condition is true, the table will also be populated, otherwise it will be empty. The next statement will populate the created table in case the condition was not true.

StackExchange Database Administrators Q#317592, answer score: 4