How to insert SQL query result into a temp table with example

Last Updated: 2022-12-24 14:07:55

Inserting the result of a SELECT statement into a temporary table allows you to store and manipulate the data in a table-like structure for a specific session. Temporary tables are useful when you need to store intermediate results or perform complex queries that involve multiple steps. They can also be used to improve the performance of queries by allowing you to index and join the data in a temporary table.

 

To insert the result of a SELECT statement into a temporary table in SQL, you can use the CREATE TEMPORARY TABLE and INSERT INTO statements. The CREATE TEMPORARY TABLE statement creates a temporary table with a given name and structure, and the INSERT INTO statement inserts rows into the table. The rows to be inserted can be specified using a SELECT statement, which retrieves rows from one or more tables based on a given condition.

 

Temporary tables are only visible to the current session and are automatically dropped when the session ends. However, you can drop a temporary table manually using the DROP TABLE statement if needed.

 

To insert the result of a SELECT statement into a temporary table in SQL, you can use the following syntax:

CREATE TEMPORARY TABLE temp_table_name (column_list);

INSERT INTO temp_table_name
SELECT * FROM original_table WHERE condition;

 

Here's an example:

CREATE TEMPORARY TABLE temp_sales (id INT, product VARCHAR(255), quantity INT, price DECIMAL(10,2));

INSERT INTO temp_sales
SELECT id, product, quantity, price FROM sales WHERE quantity > 5;

 

This will create a temporary table called temp_sales with the same structure as the sales table, and then insert all rows from the sales table where the quantity column is greater than 5.

Note that temporary tables are only visible to the current session and are automatically dropped when the session ends. If you want to create a table that is persisted beyond the current session, you can use the CREATE TABLE statement instead.

 

The CREATE TEMPORARY TABLE statement creates a temporary table with the given name and structure. The structure of the temporary table is defined by the column list in the parentheses, which specifies the name and data type of each column in the table.

The INSERT INTO statement is used to insert rows into a table. In this case, the INSERT INTO temp_sales clause specifies that the rows will be inserted into the temp_sales table. The SELECT * FROM original_table WHERE condition clause specifies the rows that will be inserted. The SELECT statement retrieves rows from the original_table, and the WHERE clause filters the rows based on the specified condition. In this example, only rows from the sales table where the quantity column is greater than 5 will be inserted into the temp_sales table.

Together, these statements create a temporary table and insert rows from another table into it based on a specified condition. The temporary table can then be used in subsequent queries just like any other table.

 

MySQL insert SQL query result into a temp table

-- create temporary table
CREATE TEMPORARY TABLE temp_sales (id INT, product VARCHAR(255), quantity INT, price DECIMAL(10,2));

-- insert rows into temporary table
INSERT INTO temp_sales
SELECT * FROM sales WHERE quantity > 5;

Microsoft SQL Server insert SQL query result into a temp table

-- create temporary table
CREATE TABLE #temp_sales (id INT, product VARCHAR(255), quantity INT, price DECIMAL(10,2));

-- insert rows into temporary table
INSERT INTO #temp_sales
SELECT * FROM sales WHERE quantity > 5;

 

Note that in Microsoft SQL Server, you can also use the INTO keyword instead of the INTO clause:

 

SELECT * INTO #temp_sales
FROM sales WHERE quantity > 5;

oracle insert SQL query result into a temp table

CREATE GLOBAL TEMPORARY TABLE temp_sales (id INT, product VARCHAR(255), quantity INT, price DECIMAL(10,2)) ON COMMIT DELETE ROWS;

INSERT INTO temp_sales
SELECT * FROM sales WHERE quantity > 5;

 

In Oracle, temporary tables are created using the CREATE GLOBAL TEMPORARY TABLE statement. The ON COMMIT DELETE ROWS clause specifies that the rows in the temporary table will be deleted automatically when the transaction is committed.

 

Drop temporary table

 

In most cases, you do not need to drop temporary tables manually, as they are automatically dropped when the session ends. In other words, temporary tables are automatically deleted when the connection to the database is closed.

However, there may be cases where you want to drop a temporary table before the session ends. For example, you may want to do this if you are reusing the same temporary table name in multiple queries and want to ensure that the table is empty before running each query.

 

To drop a temporary table in MySQL, you can use the DROP TABLE statement:

DROP TABLE temp_sales;

 

To drop a temporary table in Microsoft SQL Server, you can use the DROP TABLE statement with the # symbol:

DROP TABLE #temp_sales;

 

To drop a temporary table in Oracle, you can use the DROP TABLE statement:

DROP TABLE temp_sales;

 

It's important to note that dropping a temporary table will delete all data in the table and remove the table from the database. This operation cannot be undone, so be sure to use it carefully.