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

Question

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 1

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