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

This could be accomplished with a single line of code:

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

Now you can use the below syntax if you are using one of the new versions of SQL Server (2016+).

DROP TABLE IF EXISTS schema.yourtable(even temporary tables #...)

When you change a column in a temp table, you must drop the table before running the query again. (Yes, it is annoying. Just what you have to do.)

I have always assumed this is because the "invalid column" check is done by parser before the query is run, so it is based on the columns in the table before it is dropped..... and that is what pnbs also said.

[pmac72][1] is using GO to break down the query into batches and using an ALTER.

You appear to be running the same batch but running it twice after changing it: DROP... CREATE... edit... DROP... CREATE..

Perhaps post your **exact** code so we can see what is going on.


[1]:

[To see links please register here]

This worked for me,

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

Here tempdb.dbo(dbo is nothing but your schema) is having more importance.

Note: This also works for ## temp tables.

i.e.

IF OBJECT_ID('tempdb.dbo.##AuditLogTempTable1', 'U') IS NOT NULL
DROP TABLE ##AuditLogTempTable1

Note: This type of command only suitable post SQL Server 2016.
Ask yourself .. Do I have any customers that are still on SQL Server 2012 ?

DROP TABLE IF EXISTS ##AuditLogTempTable1

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**][1]

**Syntax**

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

**Query:**

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


[1]:

[To see links please register here]