SQL - create temp table in if else condition but error with aleady object

Question

i wish to insert different sql result into SAME tempTable but error with already TempTable object

if @abc = 1

select * into #TmpTable from TestTable where...

else if @abc = 2

select * into #TmpTable from TestTable where...

But there is already an object named...

Answer 1

Hi @WONG Tony

select * into #TmpTable from TestTable where...

SELECT INTO statement is one of the easy ways to create a new table and then copy the source table data into this newly created table.

The insertion statements for two different conditions in your If statement use the SELECT INTO statement. I am not sure if this is the source of the problem. (There is not enough data to test).

My idea is to create #TmpTable before if condition and use this insertion statement in the if condition.

insert into #TmpTable select * from TestTable where ...

Best regards,

Percy Tang

Answer 2

But there is already an object named...

You can drop the table if it exists before you start by using

DROP TABLE IF EXISTS TmpTable;
-- now start the if...

You can also use a block of code in the if and excite DROP TABLE separately in each if condition

Answer 3

Yes, this is correct. In the same batch or stored procedure, you cannot have two statements to create the same temp table, no matter you do it with CREATE TABLE or SELECT INTO. And it does not matter if the resulting schema is the same or not. It is entirely outlawed. And this is a compile-time error, not a run-time error.

The remedy is as Percy suggests, create the table explicitly with CREATE TABLE once.