Using IF / ELSE to determine a SELECT INTO statement

Question

I'm having some strange issues using IF / ELSE to determine which one or two SELECT statements to execute. The error message I'm getting when running the full statement is that my temporary table already exists, but that does not occur if I run two separate executions of two separate IF statements.

Here is the code in SQL Server:

IF (select BusinessDayCount from Calendartbl) <= 1
  BEGIN
    SELECT * into #temp1
    FROM PreviousMonthTbl
  END
ELSE
  BEGIN
    SELECT * into #temp1
    FROM CurrentMonthTbl
  END

You should add a SQL Server or T-SQL tag to this question. I think you'll get better/more responses. — Big Daddy, May 09 '13 at 18:15
Current and Previous month should have the same structure. I simplified it here to show the issue, but they both represent subqueries of the same table, for previous I'm doing a min(date) for the table and joining back to it, for current I'm doing max(date) for the table and joining back to it. It is a table that keeps data from this month and last month, [date] here represents the month corresponding to each row of data. — user2367409, May 09 '13 at 18:20

score 18 · Answer 1 · answered May 09 '13 at 18:19

18

It's a "feature" of the syntax checking in SQL Server. You simply cannot "create" a #temporary table twice within the same batch.

This is the pattern you need.

SELECT * into #temp1
FROM PreviousMonthTbl
WHERE 1=0;

IF (select BusinessDayCount from Calendartbl) <= 1
  BEGIN
    INSERT into #temp1 SELECT *
    FROM PreviousMonthTbl
  END
ELSE
  BEGIN
    INSERT into #temp1 SELECT *
    FROM CurrentMonthTbl
  END

If you prefer, you can also express the branch (in this case) as a WHERE clause:

SELECT * into #temp1
FROM PreviousMonthTbl
WHERE (select BusinessDayCount from Calendartbl) <= 1
UNION ALL
SELECT *
FROM CurrentMonthTbl
WHERE isnull((select BusinessDayCount from Calendartbl),2) > 1

answered May 09 '13 at 18:19

RichardTheKiwi

105,798
26
196
262

Ah, I see. It wants the table to already exist and just be updated. Thanks for the assist! – user2367409 May 09 '13 at 18:23
The problem is that SQL Server doesn't determine that the two "create"s are in different branches of an IF (and even then you can actually use `label:`s to actually get both to run!). It therefore concludes that you are trying to create it twice and complain. Both solutions above get around the issue. – RichardTheKiwi May 09 '13 at 18:25
if you choose the first approach, you will need a Create Table #temp1 statement. – fbhdev May 09 '13 at 18:36
@fahed Note the `SELECT..INTO #temp1` – RichardTheKiwi May 09 '13 at 19:44
You can fake it out by using dynamic sql. However, then you'd have to use global temp tables to be able to see what was in the table. – Dan Oct 08 '18 at 19:32

Aleksandr Fedorenko · Answer 2 · 2013-05-09T19:00:53.300

You can't use SELECT INTO for a tables with same name in the same batch. Use a different name for a temporary table

IF EXISTS(
          SELECT 1    
          FROM Calendartbl
          WHERE BusinessDayCount <= 1
          )
BEGIN
  IF OBJECT_ID('tempdb.dbo.#PreviousMonthTbl') IS NULL DROP TABLE dbo.#PreviousMonthTbl
  SELECT *
  INTO #PreviousMonthTbl
  FROM PreviousMonthTbl
END
ELSE
BEGIN
  IF OBJECT_ID('tempdb.dbo.#CurrentMonthTbl') IS NULL DROP TABLE dbo.#CurrentMonthTbl  
  SELECT *
  INTO #CurrentMonthTbl
  FROM CurrentMonthTbl
END

score 0 · Answer 3 · edited May 23 '17 at 12:17

0

From what I understand the problem is this:

When you run the below statement,

SELECT * into #temp1 FROM CurrentMonthTbl

you are creating a temp table on the fly.

If before that line you had a create table statement, then this Select into statement will fail because the table already exists.

If in your case you already have a temp table created, then try replacing:

SELECT * into #temp1 FROM CurrentMonthTbl

with:

Insert into #temp1
    Select * from CurrentMonthTbl

Also look at There is already an object named '##Temp' in the database

edited May 23 '17 at 12:17

Community

1
1

answered May 09 '13 at 18:27

fbhdev

526
7
17

Looking it over, the below answer is correct, particularly the comment about how sql server sees two tables with the same name being created, ignoring the fact that the conditions under which the tables are created are mutually exclusive. – fbhdev May 09 '13 at 18:35

score 0 · Answer 4 · answered Sep 01 '21 at 06:53

You can use actual table in place of #temp1 table in if else statement. After that you can insert the data from actual to temp table and drop the actual table.

IF OBJECT_ID('tempdb..#temp1') is not null
    drop table #temp1

IF (select BusinessDayCount from Calendartbl) <= 1
BEGIN
    SELECT * into dbo.TempTable 
    FROM PreviousMonthTbl
END
ELSE
BEGIN
    SELECT * into dbo.TempTable
    FROM CurrentMonthTbl
END

select * into #temp1
from dbo.TempTable

IF OBJECT_ID('dbo.TempTable', 'U') is not null
    drop table dbo.TempTable

Using IF / ELSE to determine a SELECT INTO statement

4 Answers4

Linked