Using IF / ELSE to determine a SELECT INTO stateme

问题:

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

回答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

回答2:

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

回答3:

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