Temporary table in SQL server causing ' There is already an object named' error

sql sql-server tsql temp-tables
Art F picture Art F · Aug 19, 2013 · Viewed 189.5k times · Source

I have the following issue in SQL Server, I have some code that looks like this:

DROP TABLE #TMPGUARDIAN
CREATE TABLE #TMPGUARDIAN(
LAST_NAME NVARCHAR(30),
FRST_NAME NVARCHAR(30))  

SELECT LAST_NAME,FRST_NAME INTO #TMPGUARDIAN  FROM TBL_PEOPLE

When I do this I get an error 'There is already an object named '#TMPGUARDIAN' in the database'. Can anyone tell me why I am getting this error?

Answer

Hart CO picture Hart CO · Aug 19, 2013

You are dropping it, then creating it, then trying to create it again by using SELECT INTO. Change to:

DROP TABLE #TMPGUARDIAN
CREATE TABLE #TMPGUARDIAN(
LAST_NAME NVARCHAR(30),
FRST_NAME NVARCHAR(30))  

INSERT INTO #TMPGUARDIAN 
SELECT LAST_NAME,FRST_NAME  
FROM TBL_PEOPLE

In MS SQL Server you can create a table without a CREATE TABLE statement by using SELECT INTO

Related questions

TSQL select into Temp table from dynamic sql

This seems relatively simple, but apparently it's not. I need to create a temp table based on an existing table via the select into syntax: SELECT * INTO #TEMPTABLE FROM EXISTING_TABLE The problem is, the existing table name is accepted …

Read More
Is it possible to have temp tables in a function?

Apparently, I can't use them. I'm getting an error message like: Invalid use of a side-effecting operator 'SELECT' within a function If I want to do something like this: select bleh into #temp from Blah ... inside a function.

Read More
Update from Temp Table

Query: SELECT ID, T.c.value('@Address', 'nvarchar(20)' ) as Address INTO #TMP FROM TABLE1 CROSS APPLY XMLData.nodes('/Document') AS T(c) UPDATE TABLE1 SET HomeAddress = (SELECT TOP 1 t.Address FROM #TMP t WHERE t.ID = ID) …

Read More