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

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

DROP TABLE #TMPGUARDIANCREATE 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

I usually put these lines at the beginning of my stored procedure, and then at the end.

It is an "exists" check for #temp tables.

IF OBJECT_ID('tempdb..#MyCoolTempTable') IS NOT NULLbegin        drop table #MyCoolTempTableend

Full Example:

CREATE PROCEDURE [dbo].[uspTempTableSuperSafeExample]ASBEGIN    SET NOCOUNT ON;    IF OBJECT_ID('tempdb..#MyCoolTempTable') IS NOT NULL    BEGIN            DROP TABLE #MyCoolTempTable    END    CREATE TABLE #MyCoolTempTable (        MyCoolTempTableKey INT IDENTITY(1,1),        MyValue VARCHAR(128)    )      INSERT INTO #MyCoolTempTable (MyValue)        SELECT LEFT(@@VERSION, 128)        UNION ALL SELECT TOP 10 LEFT(name, 128) from sysobjects    SELECT MyCoolTempTableKey, MyValue FROM #MyCoolTempTable    IF OBJECT_ID('tempdb..#MyCoolTempTable') IS NOT NULL    BEGIN            DROP TABLE #MyCoolTempTable    END    SET NOCOUNT OFF;ENDGO

You must modify the query like this

CREATE TABLE #TMPGUARDIAN(LAST_NAME NVARCHAR(30),FRST_NAME NVARCHAR(30))  INSERT INTO #TMPGUARDIAN(FRST_NAME,LAST_NAME)SELECT LAST_NAME,FRST_NAME  FROM TBL_PEOPLE

-- Make a last session for clearing the all temporary tables. always drop at end. In your case, sometimes, there might be an error happen if the table is not exists, while you trying to delete.

DROP TABLE #TMPGUARDIAN

Avoid using insert into Because If you are using insert into then in future if you want to modify the temp table by adding a new column which can be filled after some process (not along with insert). At that time, you need to rework and design it in the same manner.

Use Table Variable http://odetocode.com/articles/365.aspx

declare @userData TABLE( LAST_NAME NVARCHAR(30),    FRST_NAME NVARCHAR(30))

AdvantagesNo need for Drop statements, since this will be similar to variables. Scope ends immediately after the execution.