Temp table already exists

Question

Hi I have a temporary table in which I am trying to insert records based on a where condition but it throws an error that it already exists. I have tried to change the names but that is not the issue as the temporary tables are delete when the session ends.

I think I am writing the query right.

   SELECT [Name]
        INTO #TEMP_REJECT
        FROM #TEMP_VALIDATION
        WHERE Name = @Name

I am trying to insert #TEMP_REJECT FROM #TEMP_VALIDATION

Error message

"There is already an object named '#TEMP_REJECT' in the database."

Please suggest.

Thank you for your help. R

Answer 1

SQL Server won't tell you that a table doesn't exist if it doesn't.

I suggest that you add

IF OBJECT_ID('tempdb..#TEMP_REJECT') IS NOT NULL
    DROP TABLE #TEMP_REJECT

in front of your select statement. This guarantees that the temp table won't exist when the select is executed.

So your statement becomes

 IF OBJECT_ID('tempdb..#TEMP_REJECT') IS NOT NULL
      DROP TABLE #TEMP_REJECT

 SELECT [Name]
            INTO #TEMP_REJECT
            FROM #TEMP_VALIDATION
            WHERE Name = @Name