SQL Insert into Temp Table in Both If and Else Blocks

SQL Insert Into Temp Table in both If and Else Blocks

The problem you’re having is not that you are populating the temp table, but that you’re trying to create the table. SQL parses your script and finds that you are attempting to create it in two different places, and so raises an error. It is not clever enough to realize that the “execution path” cannot possibly hit both of the create statemements. Using dynamic SQL will not work; I tried

DECLARE @Command  varchar(500)

DECLARE @Id int 
SET @Id = 2

IF OBJECT_ID('tempdb..#MyTestTable') IS NOT NULL DROP TABLE #MyTestTable 

IF (@Id = 2) BEGIN  
    SET @Command = 'SELECT ''ABC'' AS Letters INTO #MyTestTable'
END ELSE BEGIN 
    SET @Command = 'SELECT ''XYZ'' AS Letters INTO #MyTestTable'
END 

EXECUTE (@Command)

select * from #MyTestTable

but the temp table only lasts as long as the dynamic session. So, alas, it looks like you’ll have to first declare the table and then populate it. Awkward code to write and support, perhaps, but it will perform efficiently enough.

How to create same Temporary table in ELSE IF Statement in SQL Server?

You need to create the temp table first.

Then use INSERT..INTO in any IF..ELSE statement.

using table variable is not a good idea as it will have performance issue.

To create temp table easily, use below code in the beginning of your script

-- check if table exists
IF OBJECT_ID('tempdb..#tempQuantity') IS NULL
    DROP TABLE #tempQuantity

-- simply create the temp table using 1=2 in where clause
SELECT 
    Year(Time),
    ItemID,
    StoreID,
    sum(Qty) Quantity,
    sum(ExtendedPrice) ExtendedPrice,
    sum(ExtendedCost) ExtendedCost
into #tempQuantity
FROM 
    dbo.F_ItemDailySalesParent
where 1=2

Then use INSERT..INTO instead of SELECT..INTO in all your IF conditions

Working with IF/ELSE and inserting into temp tables

This is actually very simple, you aren't giving a length to @contenttype, as such, by default it's taking a length of 1 char.

This will be clear if you do this:

DECLARE @contentID int, @contenttype varchar
SET @contentid = 28861
SET @contenttype = 'resource'
SELECT @contenttype 

The result is r, so, IF (@contenttype = 'resource') is not true

select into a temporary table in ms sql query if else block

This is a parser issue. You have 2 statements that attempt to create the object #TheTempTable in your batch. It doesn't matter that the 2 statements cannot be both be reached, the parser isn't "that clever". This can be replicated with this simple batch:

IF 1 = 1 
    SELECT 1 AS I INTO #T
ELSE
    SELECT 2 AS I INTO #T;

Msg 2714, Level 16, State 1, Line 4

There is already an object named '#T' in the database.

Instead, create the table outside of your IF statements, and then INSERT into it:

DROP TABLE IF EXISTS #TheTempTable

SELECT PartNumber
INTO #TheTempTable
FROM MainPartsTable
WHERE 1 = 0; --Never true

IF DATENAME(MONTH, GETDATE()) IN (N'March', N'April')
BEGIN
        INSERT INTO #TheTempTable (PartNumber)
        SELECT PartNumber 
        FROM MainPartsTable
        UNION ---ALL?
        SELECT '999';
END;
ELSE 
BEGIN
        INSERT INTO #TheTempTable (PartNumber)
        SELECT PartNumber 
        FROM MainPartsTable
END;

SELECT * FROM #TheTempTable;

DROP TABLE IF EXISTS #TheTempTable;

Of course, the entire thing could be simplified to the below:

SELECT PartNumber
INTO #TheTempTable
FROM(SELECT PartNumber 
     FROM MainPartsTable
     UNION ALL
     SELECT '999'
     WHERE DATEPART(MONTH, GETDATE()) IN (3,4))PN; --Language agnostic

SQL IF and ELSE block with temp tables inside

Why not this ?

IF @parameter = 'Option1' BEGIN

INSERT INTO #table1
select * from... where 'condition_for_Option1'
INSERT INTO #table2
select * from... where 'condition_for_Option1'

select * from (

select * from #table1
union all
select * from #table2) as DATA
END 
ELSE

IF @parameter = 'Option2' BEGIN

INSERT INTO #table1
select * from... where 'condition_for_Option2'
INSERT INTO #table2
select * from... where 'condition_for_Option2'
SELECT *
FROM
(
select * from #table1
union all
select * from #table2)
as DATA
END 

Temporary Table in if statement

IF @someVariable = 0
BEGIN
   SELECT * 
   INTO #TempTable1
   FROM MyTable
   WHERE Category="Something"
   DROP TABLE #TempTable1
 END
ELSE
 BEGIN
   SELECT * 
   INTO #TempTable2
   FROM MyTable
   WHERE Category="SomethingElse"
   DROP TABLE #TempTable2
END

This will work, but depending on what else you may need the code to do, might not be the best solution; but in your example code it solves the problem.

Alternatively:

SELECT * into #TempTable from MyTable where (1=0)

IF @someVariable = 0
   INSERT INTO #TempTable SELECT * FROM MyTable WHERE Category="Something"
ELSE
   INSERT INTO #TempTable SELECT * FROM MyTable WHERE Category="OtherSomething"

DROP TABLE #TempTable

T-SQL If Else condition on the same Temp Table

This was a fun problem for me that is... Well I figured out four ways to do it. One is with a view, one with a temp Table, one with a physical table, and one with a stored procedure and global temp table. Let me know if you have any questions.

View

DECLARE @Variable VARCHAR(10) = 'aa';

IF LEN(@Variable) > 1
BEGIN
    EXEC('CREATE VIEW yourView AS SELECT ''Greater than 1'' col')
END
ELSE
BEGIN
    EXEC('CREATE VIEW yourView AS SELECT ''Less than 1'' col')
END

SELECT *
FROM yourView;

DROP VIEW yourView;

Temp Table

DECLARE @Variable VARCHAR(100) = 'aa',
        --Default value is 0
        @percent INT = 0;

--If the length > 1, then change percent to 100 as to return the whole table
IF LEN(@Variable) > 1
    SET @percent = 100;

--If the length <=1, then @percent stays 0 and you return 0 percent of the table
SELECT TOP(@percent) PERCENT 'Greater than 1' col INTO #TEMPTAB

--If you didn't populate the table with rows, then use this query to populate it
IF(@percent = 0)
BEGIN
    INSERT INTO #TEMPTAB
    SELECT 'Less than 1' col
END

/*your 1k lines of code here*/
SELECT *
FROM #TEMPTAB

--Cleanup
DROP TABLE #tempTab

Physical Table

DECLARE @Variable VARCHAR(10) = 'A';

IF len(@Variable) > 1
BEGIN
   SELECT 'Greater than 1' col INTO TEMPTAB 
END
ELSE
BEGIN
    SELECT 'Less than 1' col INTO TEMPTAB2
END

IF OBJECT_ID('TEMPTAB2') IS NOT NULL
    --SP_Rename doesn't work on temp tables so that's why it's an actual table
    EXEC SP_RENAME 'TEMPTAB2','TEMPTAB','Object'

SELECT *
FROM TEMPTAB

DROP TABLE TEMPTAB;

Stored Procedure with Global Temp Table

IF OBJECT_ID('yourProcedure') IS NOT NULL
        DROP PROCEDURE yourProcedure;
GO
CREATE PROCEDURE yourProcedure
AS
    IF OBJECT_ID('tempdb..##TEMPTAB') IS NOT NULL
        DROP TABLE ##tempTab;
    SELECT 'Greater than 1' col INTO ##TEMPTAB
GO

DECLARE @Variable VARCHAR(10) = 'aaa';

IF LEN(@Variable) > 1
    BEGIN
        EXEC yourProcedure; 
    END
ELSE
    BEGIN
        SELECT 'Less than 1' col INTO ##TEMPTAB
    END

SELECT *
FROM ##TEMPTAB

IF OBJECT_ID('tempdb..##TEMPTAB') IS NOT NULL
        DROP TABLE ##TEMPTab;

---

Related Topics