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

Can you give us the exact error message you encounter ? Also give us the table definitions. — Hybris95, Apr 25 '17 at 08:19
Hi I have updated the question with error message 'There is already an object named '#TEMP_REJECT' in the database.' — BRDroid, Apr 25 '17 at 08:21
Off topic, it might be worth considering table variables instead - http://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server — user5226582, Apr 25 '17 at 08:24

score 6 · Answer 1 · edited May 23 '17 at 12:09

6

This answer will help you : https://stackoverflow.com/a/8560644/3635715

To make short : SELECT INTO creates table then insert records. INSERT INTO only insert the records.

So in your case, since #TEMP_REJECT already exists, SELECT INTO is rejected because it cannot create the table again, so you have to use INSERT INTO after first SELECT INTO.

IF OBJECT_ID('tempdb..#TEMP_REJECT') IS NOT NULL
BEGIN
  INSERT INTO #TEMP_REJECT
  SELECT [Name]
  FROM #TEMP_VALIDATION
  WHERE Name = @Name
END
ELSE
BEGIN
  SELECT [Name]
  INTO #TEMP_REJECT
  FROM #TEMP_VALIDATION
  WHERE Name = @Name
END

References :
INTO Clause
INSERT Clause

edited May 23 '17 at 12:09

Community

1
1

answered Apr 25 '17 at 08:26

Hybris95

2,286
2
16
33

Isn't there a way of doing this without duplicating code? – christiaantober Dec 24 '21 at 13:44
Of course, you can drop the temporary table before doing SELECT INTO if the temporary table already exists – Hybris95 Jan 20 '22 at 09:50

score 6 · Accepted Answer · answered Apr 25 '17 at 08:29

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

score 1 · Answer 3 · answered Apr 25 '17 at 08:29

Try checking before inserting

if object_id('tempdb..#TEMP_REJECT') is not null drop table #TEMP_REJECT

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

if SQL says,there is temp table,there must be a table prior to your insert

score 1 · Answer 4 · answered Jun 07 '20 at 23:28

I've noticed that I get the same error when I am working consecutively through the same SQL Server tab/file.

For example, I have a large SQL file that I use to execute a series of updates using temp tables. When I execute it consecutively, it errors out complaining that my #tempTable already exists (even if I use a "if this table exists, drop it" statement as DeanOC describes).

Exiting out of the file/tab between consecutive runs resolves the issue.

If this poses an issue for your use case, I'd suggest bundling your statements in a stored proc and implementing DeanOC's drop logic therein.

Yes, apparently this was also my issue when running a large SQL file in SQL Server Management Studio. NOTE: It may fix the problem to surround your SQL with BEGIN/END — Design.Garden, Jul 28 '20 at 19:46

Temp table already exists

4 Answers4