intro
Let’s explore everything you need to know about the SQL SELECT INTO TEMP TABLE mechanism in MySQL, PostgreSQL, SQL Server, and Oracle.
Sometimes, you need to create a new table, but you already have all the data you need stored in the database. That is especially common when working with temporary tables. In these cases, the goal is to create and populate a temporary table with useful aggregated data to simplify a complex query. That is exactly what the SQL SELECT INTO TEMP TABLE mechanism is designed for.
Follow this guide to learn how to use SELECT INTO with temporary tables to define and fill them in a single SQL statement!
What Does It Mean to Select into a Temp Table in SQL?
Before answering this question, you need to understand what the SQL SELECT INTO clause does and what temporary tables are.
In short, the SELECT INTO statement copies data from one table into a new table. Instead, a temporary table (often called a “temp table”) is a special type of table that exists only for the duration of your session or transaction.
So, selecting into a temp table means creating a short-lived temporary table and immediately populating it with the result of a query. In other words, this is a mechanism to quickly define and fill a new temporary table.
SQL SELECT INTO TEMP TABLE Syntax
The SQL SELECT INTO TEMP TABLE mechanism does not follow a universal syntax. In particular, each major database system implements it differently.
Let’s explore how to perform this operation in MySQL, PostgreSQL, SQL Server, and Oracle!
MySQL
MySQL does not support the SELECT INTO syntax (though it does support INSERT INTO). Thus, you cannot use the SELECT INTO TEMP TABLE pattern in MySQL.
On the contrary, you can achieve the same result using the following syntax:
1
CREATE TEMPORARY TABLE temp_table AS
2
SELECT column_1, column_2, column_n
3
FROM original_table
4
[...];
This creates a temporary table called temp_table and populates it with data from original_table. Note that the AS keyword is followed by a standard MySQL SELECT statement, which can include WHERE, GROUP BY, HAVING, ORDER BY clauses, and more.
PostgreSQL
PostgreSQL supports the SQL SELECT INTO syntax directly, unlike MySQL. So, you can use the Postgres SELECT INTO TEMP TABLE mechanism with the syntax outlined below:
1
SELECT column_1, column_2, ..., column_n
2
INTO temp_table
3
FROM original_table
4
[...];
This will create a temporary table in Postgres, with the data retrieved from the original_table table as specified in the FROM section of the query.
Note: Just like in MySQL, FROM can be followed by the other possible SQL clauses.
Alternatively, PostgreSQL also supports the CREATE TEMP TABLE syntax, similar to MySQL:
1
CREATE TEMPORARY TABLE temp_table AS
2
SELECT column_1, column_2, column_n
3
FROM original_table
4
[...];
This approach also creates a temporary table and populates it with the results of the SELECT query.
SQL Server
The SQL Server SELECT INTO TEMP TABLE syntax is:
1
SELECT column_1, column_2, ..., column_n
2
INTO #temp_table
3
FROM original_table
4
[...];
Note: In SQL Server, the # prefix denotes a local temporary table, which is only accessible within the current session. The ## prefix denotes a global temporary table, which is accessible to all sessions until the creating session ends. Learn more in our dedicated guide on the SQL Server temporary table mechanism.
Oracle
Oracle supports the SELECT INTO statement, but only to select values into variables within PL/SQL, not to create tables.
To create and populate a temporary table in Oracle, you must first create the table explicitly:
1
CREATE GLOBAL TEMPORARY TABLE temp_table (
2
column_1 datatype,
3
column_2 datatype,
4
...
5
column_n datatype,
6
) ON COMMIT PRESERVE ROWS;
Then, insert data into it with a separate INSERT INTO ... SELECT statement:
1
INSERT INTO temp_table
2
SELECT column_1, column_2
3
FROM original_table
4
[...];
As of the time this blog is written, Oracle does not support a direct SELECT INTO TEMP TABLE mechanism for creating and populating temporary tables in a single step.
SELECT INTO TEMP TABLE: Examples
To better understand how this mechanism works, let’s walk through some practical examples!
The following queries in this section will be written for PostgreSQL, which supports both SELECT INTO TEMP TABLE and CREATE TEMPORARY TABLE ... AS syntax. Still, you can easily adapt them to MySQL or SQL Server using the syntax variations explained earlier in this guide.
Note: These sample queries below will be run in DbVisualizer, a top-rated database client that supports over 50 databases. However, you can run them in any PostgreSQL client.
In detail, the original table we will use to create temporary tables from is called orders:
This table contains sample order data. The goal is to aggregate this data to calculate the total orders per day and store the results in a temporary table.
Time to see how to achieve that!
SELECT INTO TEMP TABLE
Create a temporary table that aggregates the total order amount by day using SELECT INTO:
1
SELECT customer_id, DATE(placed_at) AS order_date, SUM(total) AS daily_total
2
INTO TEMP TABLE daily_sales_summary
3
FROM orders
4
GROUP BY customer_id, DATE(placed_at)
5
ORDER BY order_date;
Note that DATE(placed_at) converts the timestamp into a regular date.
Execute the above query:
As you can see in DbVisualizer, this creates a temporary table with six rows.
You can now write queries like this:
1
SELECT * FROM daily_sales_summary
2
WHERE order_date = '2025-01-01';
That retrieves the aggregated daily order data for January 2025 in a simplified way by using a temporary table created and populated with the SELECT INTO mechanism.
CREATE TEMPORARY TABLE ... AS
Equivalently, you can achieve the same result as before using the CREATE TEMPORARY TABLE ... AS syntax:
This will contain:
Et voilà! Mission complete.
Conclusion
In this guide, you learned how the SELECT INTO TEMP TABLE mechanism works across the most popular SQL databases. Specifically, it allows you to create a temporary table and immediately populate it with data. You also discovered how to use this feature in MySQL, PostgreSQL, SQL Server, and Oracle.
As shown here, DbVisualizer simplifies writing and executing SQL queries with support for over 50 databases. It also includes powerful features like SQL formatting, ER diagrams, and query optimization tools. Download DbVisualizer for free today!
FAQ
Is the syntax to SELECT INTO temp tables part of the ANSI SQL standard?
No, the syntax for selecting into temporary tables is not part of the ANSI SQL standard. That is because each database system handles temporary tables differently, let alone the SQL SELECT INTO TEMP TABLE syntax.
What is the difference between CREATE TEMPORARY TABLE ... AS and SELECT INTO TEMP TABLE?
Both help you create a temporary table and populate it with query results. The main difference between the two lies in syntax and database support. CREATE TEMPORARY TABLE ... AS SELECT is available in PostgreSQL and MySQL. Conversely, SELECT INTO TEMP TABLE is a clause supported by systems like SQL Server and PostgreSQL.
What is the SQL Server SELECT INTO TEMP TABLE syntax?
In SQL Server, you can create and populate a temporary table in one step using the SELECT INTO syntax as in this example:
1
SELECT customer_id, total_amount
2
INTO #high_value_orders
3
FROM orders
4
WHERE total_amount > 1000;
This query creates an SQL Server local temporary table called #high_value_orders and fills it with data from the orders table, including only rows where the total_amount exceeds 1000.
Why use a database client?
A visual database client such as DbVisualizer streamlines database tasks by making it easy to query, manage, and visualize data. It provides a user-friendly interface for working with tables, exploring schema relationships, and crafting or troubleshooting queries through its powerful built-in SQL editor. Notable features like autocomplete, ER diagrams, and data export utilities help DbVisualizer stand out. Try the Pro version with a 21-day free trial!
