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

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 #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

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 NULL
begin
        drop table #MyCoolTempTable
end

Full Example:

CREATE PROCEDURE [dbo].[uspTempTableSuperSafeExample]
AS
BEGIN
    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;
END
GO

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

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)
)

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