Drop Temp Table in SQL Server and PostgreSQL

This article explains different ways to drop the temp table in SQL Server and PostgreSQL. As the name suggests, temporary tables are used to store the data temporarily. It can be used to process the data without accessing the actual table. We can create indexes, statistics, and constraints on the temp tables.

Temporary Tables in SQL Server

The temp tables are stored in the TempDB database of the SQL Server. There are two types of temporary tables supported in the SQL Server.

1. Global Temporary table: The global temporary tables can be accessed from the different sessions. The global temporary tables will be dropped when we restart the SQL Server services.
2. Local Temporary table: The local temporary tables can be accessed in the session which has created it. Another session cannot access it. The local temp table will be dropped when you close the connection or session.

The syntax to create a temporary table is as follows:

CREATE TABLE #tblname | ##tblname
  ( 
     column_1 [DATA TYPE], 
     column_2 [DATA TYPE], 
     column_3 [DATA TYPE], 
     column_4 [DATA TYPE] 
  ) 

As you can see, in the following syntax, we need to specify the # or ## before the name of the table. When you specify the table name with # prefix, the local temporary table will be created. When you use the ## prefix, the global temporary table will be created. For example, we want to create a local temporary table named #tblPatient, so the CREATE TABLE statement is as follows:

CREATE TABLE [#tblpatient] 
  ( 
     [patient_id]      [BIGINT] IDENTITY(1, 1) NOT NULL, 
     [patient_code]    [VARCHAR](50) NOT NULL, 
     [patient_name]    [VARCHAR](50) NULL, 
     [address]         [VARCHAR](25) NULL, 
     [city]            [VARCHAR](50) NULL, 
     [appointmentdate] [DATETIME] NULL 
  ) 

Once the temp table is created, you can run the following query on the TempDB database to view the table.

USE tempdb 
go 
SELECT NAME, create_date FROM   sys.tables 

Query Output

We can view the temp table in SQL Server Management Studio. Launch SQL Server Management studio ? Expand Databases ? Expand System Database ? Expand TempDB ? Expand Temporary Tables. See the following image:

How to Drop Temp Table in SQL Server

We can drop the temporary table by using any of the following methods:

Method 1:

We can use the OBJECT_ID function. The OBJECT_ID function returns the object name from the database. Here, we must use the three-part name to locate the table. To get the table name from a specific database, we can write the SQL statement as follows:

SELECT Object_id('VSDatabase.dbo.tblPatient') 

The query returns the object id of the table name specified. See the output below.

Suppose we want to drop a table named tblPatient from VSDatabase, the query looks like this:

USE master 
go 
IF Object_id('VSDatabase.dbo.tblPatient') IS NULL 
  BEGIN 
      DROP TABLE [VSDatabase].[dbo].[tblpatient] 
  END 
CREATE TABLE [dbo].[tblpatient] 
  ( 
     [patient_id]      [BIGINT] IDENTITY(1, 1) NOT NULL, 
     [patient_code]    [VARCHAR](50) NOT NULL, 
     [patient_name]    [VARCHAR](50) NULL, 
     [address]         [VARCHAR](25) NULL, 
     [city]            [VARCHAR](50) NULL, 
     [appointmentdate] [DATETIME] NULL 
  )  

We want to drop a table named #tblPatient. The #tblPatient is created in the TempDB database. We will use the following query to identify and drop the #tblPatient table.

USE master 
go 
IF Object_id('TempDB.dbo.#tblPatient') IS NULL 
  BEGIN 
      DROP TABLE [TempDB].[dbo].[#tblpatient] 
  END 
CREATE TABLE [#tblpatient] 
  ( 
     [patient_id]      [BIGINT] IDENTITY(1, 1) NOT NULL, 
     [patient_code]    [VARCHAR](50) NOT NULL, 
     [patient_name]    [VARCHAR](50) NULL, 
     [address]         [VARCHAR](25) NULL, 
     [city]            [VARCHAR](50) NULL, 
     [appointmentdate] [DATETIME] NULL 
  ) 

Now, let us see another method.

Method 2:

In SQL Server 2016 and above versions, we can use the DROP TABLE IF EXISTS statement. The SQL DROP TABLE IF EXISTS statement checks if the table/object exists on the database.

Suppose you want to drop the tblPatient table from the VSDatabase database. The query should be written as follows:

USE master 
go 
DROP TABLE IF EXISTS [tblpatient] 
GO
CREATE TABLE [dbo].[tblPatient](
    [Patient_ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Patient_code] [varchar](50) NOT NULL,
    [Patient_name] [varchar](50) NULL,
    [Address] [varchar](25) NULL,
    [City] [varchar](50) NULL,
    [AppointmentDate] [datetime] NULL
    )

As shown in the above query, search the tblPatient table in the VSDatabase database. If a table exists, the query will drop the table.

To drop the temporary table named #tblPatient, the query is written as follows:

USE master 
go 
DROP TABLE IF EXISTS [#tblpatient] 
GO
CREATE TABLE [dbo].[#tblPatient](
	[Patient_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Patient_code] [varchar](50) NOT NULL,
	[Patient_name] [varchar](50) NULL,
	[Address] [varchar](25) NULL,
	[City] [varchar](50) NULL,
	[AppointmentDate] [datetime] NULL
	)

Now, let us explore the temporary tables in the PostgreSQL database.

Temporary Tables in PostgreSQL

The temporary table is supported in PostgreSQL. The syntax to create a PostgreSQL temporary table like this:

CREATE TEMPORARY TABLE tblName
(
	Column_1 [Data type],
	Column_2 [Data Type],
	Column_3 [Data Type]	
)

As you can see in the above syntax, while creating a temporary table, we must specify the keyword TEMP or TEMPORARY in CREATE TABLE statement. Suppose you want to create a temporary table named tblCountries. The syntax is as follows:

CREATE TEMPORARY TABLE tblCountries
(
	Country_id int,
	COuntry_Code varchar(500),
	Country_Name varchar(1000)	
)

Query Output

We can view the data by running a SELECT statement:

SELECT * FROM tblCountries

Query Output

Temporary tables are session-specific, so it is accessible by the session which has created it. To understand that, launch another query editor window and run a SELECT statement to populate data from tblCountries.

As you can see, the query encountered an error that explains that the temporary tables cannot be accessed by different sessions.

The temporary tables in the PostgreSQL are created in a pg_temp schema, but when we drop the temp table, we do not have to specify the schema name. The following query provides the list of temporary tables created in the database.

SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname like '%pg_temp%';

Query Output

How to Drop Temp Table in PostgreSQL

The DROP TABLE query is used to drop the temporary table. Unlike the CREATE TEMPORARY TABLE statement, we do not have to specify the TEMPORARY keyword in the DROP TABLE statement. Let us drop the tblCountries table in the following query:

DROP TABLE tblCountries

Execute the following query to populate the list of temporary tables.

SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname like '%pg_temp%';

Query Output

As you can see, the table tblCountries has been dropped.

In the context of managing temporary tables in PostgreSQL, dbForge Studio for PostgreSQL can significantly streamline your database operations. We highly recommend trying out dbForge Studio for PostgreSQL to experience its advanced features and user-friendly interface. You can download a free trial version from the official website and start enhancing your PostgreSQL management today.

Summary

From this article, we learned how we can create temp table in SQL Server and PostgreSQL database. In SQL Server, we can use the OBJECT_ID function to get the table name of the temporary table, and if the table is found, we can use the DROP TABLE statement to drop the temp table in sql. Another method we learned is to use the DROP TABLE IF EXISTS statement. Moreover, we learned about the temporary tables in PostgreSQL, how to create and drop them.

Tags: postgresql, sql drop table, sql server Last modified: June 27, 2023