CODEWITHSUNDEEP
sqlsql-server
Ervis Trupja
Ervis Trupja

Reputation: 2810

SQL - There is already an object named 'variableName' in the database

I have created a recursive query which looks like below. 

;WITH Hierachy(LevelID) 
 AS ( SELECT LevelID 
      FROM   tDDFLevel r 
      WHERE  r.LevelID IN 
           (SELECT LevelID 
            FROM   tDDFViewLevelMapping 
            WHERE  ViewID = 2 
           )  
      AND r.LevelID NOT IN (SELECT ObjectID FROM tSysTrashBin)  
      AND r.Parent = -1  UNION ALL SELECT c.LevelID 
      From tDDFLevel c 
        INNER Join Hierachy ch ON c.Parent = ch.LevelID  
                              And c.LevelID Not IN (SELECT ObjectID FROM tSysTrashBin) 
    )

The result from this query will always be a column of integers. What I want to do is that I want to save the result in a list of Integers so I have modified the query and now looks like:

declare @LevelIDs int  

 ;WITH Hierachy(LevelID) 
 AS ( SELECT LevelID 
      FROM   tDDFLevel r 
      WHERE  r.LevelID IN 
           (SELECT LevelID 
            FROM   tDDFViewLevelMapping 
            WHERE  ViewID = 2 
           )  
      AND r.LevelID NOT IN (SELECT ObjectID FROM tSysTrashBin)  
      AND r.Parent = -1  
      UNION ALL 
      SELECT c.LevelID 
      From   tDDFLevel c 
        INNER Join Hierachy ch ON c.Parent = ch.LevelID  
                              And c.LevelID Not IN (SELECT ObjectID FROM tSysTrashBin) 
    )  

 SELECT LevelID INTO LevelIDs FROM Hierachy  
 SELECT LevelID from LevelIDs

But, I keep getting an error which says:

Msg 2714, Level 16, State 6, Line 4 There is already an object named 'LevelIDs' in the database.

What am I missing?

Upvotes: 1

Views: 752

Answers (2)

Vicky_Burnwal
Vicky_Burnwal

Reputation: 981

You should create a table variable like below.

DECLARE @LevelIDs TABLE(
    LevelId INT   
);

Then use Insert into.

INSERT INTO @LevelIDs
SELECT LevelID FROM Hierachy

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

SELECT LevelID INTO LevelIDs FROM Hierachy

The above statement creates a new table whenever it is executed. If you want to store the result permanently then create a new table called LevelIDs and use INSERT INTO 

Insert into LevelIDs(LevelID)
SELECT LevelID  FROM Hierachy

Else if you want to store the LevelID temporarily then create a temp which will be available only to that session. Temporary tables are created with # prefixed with the table name 

Insert into #LevelIDs(LevelID)
SELECT LevelID  FROM Hierachy

Upvotes: 1

Related Questions