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

User · Answer

In Azure Data warehouse also this occurs sometimes  because temporary tables created for a user session   I got the same issue fixed by reconnecting the database

User · Answer

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

User · Answer

I usually put these lines at the beginning of my stored procedure  and then at the end  It is an  quot exists quot  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

User · Answer

Some times you may make silly mistakes like writing insert query on the same  sql file  in the same workspace tab  so once you execute the insert query where your create query was written just above and already executed  it will again start executing along with the insert query   This is the reason why we are getting the object name  table name  exists already  since it s getting executed for the second time   So go to a separate tab to write the insert or drop or whatever queries you are about to execute   Or else use comment lines preceding all queries in the same workspace like  CREATE --     -- Insert query INSERT INTO --

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to temp-tables