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

Answer 1

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