Check if a temporary table exists and delete if it exists before creating a temporary table

drop temporary table if exists mysql
create temp table
check if global temp table exists
drop temp table if exists sql server 2017
drop temp table if exists postgres
drop table if exists
drop temp table if exists redshift
sql server drop temp table in stored procedure

I am using the following code to check if the temporary table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, it will give an error saying "invalid column". Please let me know what I am doing wrong.

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

CREATE TABLE #Results
(
    Company                CHAR(3),
    StepId                TINYINT,
    FieldId                TINYINT,
)

select company, stepid, fieldid from #Results

--Works fine to this point

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

CREATE TABLE #Results
(
    Company                CHAR(3),
    StepId                TINYINT,
    FieldId                TINYINT,
    NewColumn            NVARCHAR(50)
)

select company, stepid, fieldid, NewColumn from #Results

--Does not work

I cannot reproduce the error.

Perhaps I'm not understanding the problem.

The following works fine for me in SQL Server 2005, with the extra "foo" column appearing in the second select result:

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
GO
CREATE TABLE #Results ( Company CHAR(3), StepId TINYINT, FieldId TINYINT )
GO
select company, stepid, fieldid from #Results
GO
ALTER TABLE #Results ADD foo VARCHAR(50) NULL
GO
select company, stepid, fieldid, foo from #Results
GO
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
GO

How to drop temp tables in SQL Server, However, it may be necessary to drop the temp table before creating it. It is a common practice to check whether the temporary table exists or� I want to write a SQL IF statement that checks whether or not a local temporary table exists, but those kinds of tables are not recorded in the SQL Anywhere system catalog.

The statement should be of the order

  1. Alter statement for the table
  2. GO
  3. Select statement.

Without 'GO' in between, the whole thing will be considered as one single script and when the select statement looks for the column,it won't be found.

With 'GO' , it will consider the part of the script up to 'GO' as one single batch and will execute before getting into the query after 'GO'.

How do I check if #tempTable exists? - MSDN, There is no reason to check for #tempTable if you are creating the temporary table within a SP If the #temp table is not in your stored procedure, it doesn't exist. It is dropped when the procedure it resides in completes. you want to pass information into a trigger other than the inserted and deleted tables. parado I dont want to create it as #temp_ .. because i want the table to be insrted in the MDB sybase database , creating as #tem will be deleted when the sesions ends which I dont want that. its like similar in oracle global temporary table – Moudiz Jun 21 '13 at 9:00

Instead of dropping and re-creating the temp table you can truncate and reuse it

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    Truncate TABLE #Results
else
    CREATE TABLE #Results
    (
        Company             CHAR(3),
        StepId              TINYINT,
        FieldId             TINYINT,
    )

If you are using Sql Server 2016 or Azure Sql Database then use the below syntax to drop the temp table and recreate it. More info here MSDN

Syntax

DROP TABLE [ IF EXISTS ] [ database_name . [ schema_name ] . | schema_name . ] table_name [ ,...n ]

Query:

DROP TABLE IF EXISTS tempdb.dbo.#Results
CREATE TABLE #Results
  (
   Company             CHAR(3),
   StepId              TINYINT,
   FieldId             TINYINT,
  )

How To Drop Temporary Table If Exists In SQL Server?, IF EXISTS to the drop statement, you can drop the object only when it exists in the database. You can use DROP IF EXISTS to drop any temporary table as well if it So, we have to use the old technique of checking for the object using CREATE TABLE #TempTable ( ID INT, Name VARCHAR(100) ). DELIMITER $$ CREATE PROCEDURE `temp_sp1`() BEGIN CREATE TEMPORARY TABLE IF NOT EXISTS `temp_table` ( `col2` int(11) DEFAULT NULL, `col3` int(11) DEFAULT NULL ); INSERT INTO `temp_table` (`col2`, `col3`) VALUES (4, 5); SELECT `col2`, `col3` FROM `temp_table`; END$$ DELIMITER ;

I think the problem is you need to add GO statement in between to separate the execution into batches. As the second drop script i.e. IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results did not drop the temp table being part of single batch. Can you please try the below script.

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

CREATE TABLE #Results
(
    Company                CHAR(3),
    StepId                TINYINT,
    FieldId                TINYINT,
)

GO

select company, stepid, fieldid from #Results

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

CREATE TABLE #Results
(
    Company                CHAR(3),
    StepId                TINYINT,
    FieldId                TINYINT,
    NewColumn            NVARCHAR(50)
)

GO

select company, stepid, fieldid, NewColumn from #Results

Drop Temp Table If Exists, We need to check if the temp table exists within the TempDB database and if it does, we need to drop it. [cc lang=”sql”] IF OBJECT_ID(N'tempdb.. I am using the following code to check if the temporary table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column la

This could be accomplished with a single line of code:

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

How to check if Temp table exists in Sql Server?, This article shows how in Sql Server we can check the existence of Temporary Table. To demonstrate this let us first create a Temporary Table� Check if a temporary table exists and delete if it exists before creating a temporary table 2 Global Temporary Table with “On commit delete rows” is not holding any data

Check If Temporary Table or Temp Table Exists in SQL Server , SQL Server database programmers frequently create temporary tables and before creating temp table, T-SQL developer has to drop temp table after they validate� Select and Right Click the Table you wish to delete, then delete. Such a screen should be displayed. Click Update Database to confirm. This method is very safe as it gives you the feedback and will warn of any relations of the deleted table with other tables.

Checking for temporary table existence, There are many wrong ways to verify temporary table existence. I was recently reviewing a newly created T-SQL stored procedure. IF exists (select * from tempdb..sysobjects where name like '#fg%') When you run the first two statements (and remember, these are like the one in the stored procedure),� So here’s the easy solution. We need to check if the temp table exists within the TempDB database and if it does, we need to drop it. [cc lang=”sql”] IF OBJECT_ID(N’tempdb..#Temp’) IS NOT NULL BEGIN DROP TABLE #Temp END [/cc] To replicate this, let’s run the following command in the same window multiple times: [cc lang=”sql”]

The DROP TABLE IF EXISTS SQL statement explained with examples, Learn how to use the DROP TABLE IF EXISTS SQL statement to help delete your IF EXISTS SQL statement enables a check to see that the table exists prior to The TEMPORARY keyword can be used in MySQL to specify that only a�

Comments
  • Where are you adding the column? can you post the exact code that is giving you an error?
  • I am adding the column to the table #Results. If you copy the above code and run it for the first time you don't get any error. Now if you add a column to the temp table and add the column to the select statement, it will say column not found (or something like that).
  • Consider using the following pattern: BEGIN TRANSACTION; CREATE TABLE #Results; ...; DROP TABLE #Results; COMMIT. If the transaction succeeds, the table will be removed. If it fails, the table will be gone as well (since it was created within the transaction). In any case: No need to check if the table already exists.
  • Looks like you just need GO statements.
  • IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results` CREATE TABLE #Results (Company CHAR(3),StepId INT) select company, stepid from #results now go back to the create statement and add a column fieldid at the end.change select statement to include fieldid and run it.
  • 'tempdb..#name' is exactly what I needed. I was using 'dbo.#name', like a fool. I get the tempdb part, but what's with the double dots?
  • @Conrad.Dean double dot is an abbreviation for .dbo.
  • @deutschZuid it's more accurate to say that double dot is the default schema of the user, which is typically dbo (which isn't a great idea, making dbo the default schema for users but that's usually how it goes)
  • Your code is so different from the OP, that your 'cannot reproduce' statement is meaningless. I'm happy for you that you got it to work a different way.
  • This should be marked as the correct answer. It's not that the SELECT is actually going to run before the create table, It's that it is being parsed and throwing an error prior to being run, because there is an existing table called #Results that doesn't yet have the FieldId column at the time the select statement is parsed. Adding a GO in there separates the query into batches, which are each parsed & run separately.
  • I cannot believe the disparity in votes between this and the top answer, which changed the code so much - without explaining why - that it was meaningless as a response.