Using IF / ELSE to determine a SELECT INTO statement

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 #temp1FROM PreviousMonthTblWHERE 1=0;IF (select BusinessDayCount from Calendartbl) <= 1  BEGIN    INSERT into #temp1 SELECT *    FROM PreviousMonthTbl  ENDELSE  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 #temp1FROM PreviousMonthTblWHERE (select BusinessDayCount from Calendartbl) <= 1UNION ALLSELECT *FROM CurrentMonthTblWHERE isnull((select BusinessDayCount from Calendartbl),2) > 1

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 PreviousMonthTblENDELSEBEGIN  IF OBJECT_ID('tempdb.dbo.#CurrentMonthTbl') IS NULL DROP TABLE dbo.#CurrentMonthTbl    SELECT *  INTO #CurrentMonthTbl  FROM CurrentMonthTblEND

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