Temporary tables in SQL are used to store and process intermediate results within a session. They are particularly useful when you need to store temporary data that you want to manipulate or join with other tables.

Also for developing queries in complex and large databases, they could be really useful. For example, in a staging area where views build on each other, queries could be quite slow. Persist views in TempTables during a session could be a practical solution. Also for readability and what ever comes in mind.

There’s more than one way in SQL to create a temporary table, but Microsoft SQL Server (T-SQL) offers a convenient way with the ‚INTO‘ statement to create a temporary table and populate it with the results of a query in a single statement. This is often referred to as a ’select into‘ operation.

Here’s an example using a local temporary table:

SQL
-- Create and populate a local temporary table using SELECT INTO
SELECT Column1, Column2
INTO #TempTable
FROM YourSourceTable
WHERE SomeCondition;

-- Query the temporary table
SELECT * FROM #TempTable;

-- Drop the temporary table (it will be dropped automatically when the session ends)
DROP TABLE #TempTable;

In this example:

  • YourSourceTable is the source table from which you want to select data.
  • SomeCondition is a condition that filters the data you want to insert into the temporary table.

The ‚SELECT INTO‘ statement creates the ‚#TempTable‘ if it doesn’t exist and inserts the result set of the SELECT query into it.

If you want to create a global temporary table, you can use the ‚##‘ prefix:

SQL
-- Create and populate a global temporary table using SELECT INTO
SELECT Column1, Column2
INTO ##GlobalTempTable
FROM YourSourceTable
WHERE SomeCondition;

-- Query the global temporary table
SELECT * FROM ##GlobalTempTable;

-- Drop the global temporary table (it will be dropped automatically when the session ends)
DROP TABLE ##GlobalTempTable;

Note: The examples above refers to T-SQL used in Microsoft SQL Server.