CODEWITHSUNDEEP
sql-server
Nitendra Jain
Nitendra Jain

Reputation: 499

Why I am getting error in below query?

I want to select a dummy column Col1 and want to create temporary table accordingly based on the value of @no_of_col variable.

Below query is giving me error - There is already an object named '#t' in the database.

Could you please tell me why it is happening so because at a time only one condition will execute.

declare @no_of_col int
set @no_of_col = 4



if  (@no_of_col = 4) 
BEGIN

 SELECT procure1 ,procure2
INTO   #t 
    FROM   procurement
    WHERE  region = 'APAC' 
END


ELSE IF(@no_of_col = 5)
BEGIN 


 SELECT procure1,procure2, 'TestCol1' as 'Col1'
INTO   #t 
    FROM   procurement 
    WHERE  region = 'EUR'
END

Upvotes: 1

Views: 93

Answers (3)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

The Error is Generated by the SQL Compiler when the Database Engine Compiles the code, it finds 2 Definitions for the same table so will fail, Instead, you can try either of the below

Create the table and insert later

declare @no_of_col int
set @no_of_col = 4

IF OBJECT_ID('TempDb..#T') IS NOT NULL
    DROP TABLE #T

CREATE TABLE #T
(
    procure1 NVARCHAR(255),
    procure2 NVARCHAR(255),
    Col1 NVARCHAR(50)
)

IF  (@no_of_col = 4) 
BEGIN
    INSERT INTO #T
    (
       procure1,
       procure2
    )
    SELECT 
       procure1 ,
       procure2
       FROM   procurement
          WHERE  region = 'APAC' 
END
ELSE IF(@no_of_col = 5)
BEGIN 

    INSERT INTO #T
    (
       procure1,
       procure2,
       Col1
    )
    SELECT 
    procure1,
    procure2, 
    'TestCol1' as 'Col1'
    FROM   procurement 
       WHERE  region = 'EUR'
END

Or use a single insert into 

declare @no_of_col int
set @no_of_col = 4

INSERT INTO #T
(
    procure1,
    procure2
)
SELECT 
    procure1 ,
    procure2,
    Col1 = CASE region
             WHEN 'EUR' THEN 'TestCol1'
          END
    FROM   procurement
       WHERE  
       (
          @no_of_col = 4 AND region = 'APAC'
       )
       OR
       (
          @no_of_col = 5 AND region = 'EUR'
       )

Upvotes: 0

LongChalk_Rotem_Meron
LongChalk_Rotem_Meron

Reputation: 813

you probably ran the INTO command twice, hence there already exists a #t table.

INTO is like that - it only works once, and creates a table at that chance.

What you want is probably this :

INSERT INTO   #t (procure1, procure2)
SELECT procure1 ,procure2
FROM   procurement
WHERE  region = 'APAC'

or whatever the other versions are. Make sure there is already a #t table ready, when you do this. i.e. CREATE TABLE #t ....

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

The code is failing at compile time. The compiler sees that some piece of code generates a #t table and so it gains knowledge of the schema of that table in order to make sense of the rest of the code.

It then sees another piece of code that also tries to create a #t table but it already knows of one and so issues the error message.

Note that it's not, at this point in time, executing any code and so any runtime control flow is irrelevant here.

You could try and make it work with some ugly dynamic SQL but I'd recommend instead creating a single temp table with all possible columns it may require, then populating that table using INSERT ... SELECT statements rather than SELECT ... INTO and then, if you have to, just having some trick logic at the end, when you return a result set, to run different queries to return 4 or 5 columns.

In general though, SQL queries produce result sets with fixed shapes - the number, name and types of the columns - and stored procedures and other contained logic are also easier to work with when they continue in that tradition and don't try to vary the shapes of the result sets that they produce.

Upvotes: 2

Related Questions