A temporary table, also known as a temp table, is a convenient way to store and manipulate intermediate results in a SQL query. These tables are created in a tempdb database and are automatically deleted when the session that created them ends. In this article, we will explore how to create a temp table in SQL using code examples.
The basic syntax for creating a temp table in SQL is as follows:
CREATE TABLE #tempTable
(
column1 datatype,
column2 datatype,
...
);
The "#" symbol is used to indicate that this is a temp table. You can also use the "##" symbol to create a global temp table that can be accessed by multiple sessions. The column names and datatypes are specified in parentheses after the table name.
Here is an example of creating a temp table to store employee information:
CREATE TABLE #employees
(
employeeID int,
firstName varchar(50),
lastName varchar(50),
hireDate date
);
Once the temp table is created, you can insert data into it using the INSERT INTO statement:
INSERT INTO #employees
VALUES (1, 'John', 'Doe', '2022-01-01'),
(2, 'Jane', 'Smith', '2022-02-01'),
(3, 'Bob', 'Johnson', '2022-03-01');
You can also select data from the temp table using the SELECT statement:
SELECT * FROM #employees;
This will return all columns and rows from the #employees temp table.
You can also use temp tables to store intermediate results in a more complex query. For example, you can use a temp table to store the results of a subquery before joining it with another table:
CREATE TABLE #employeeSalaries
(
employeeID int,
salary int
);
INSERT INTO #employeeSalaries
SELECT employeeID, salary
FROM employees
WHERE salary > 50000;
SELECT e.employeeID, e.firstName, e.lastName, s.salary
FROM #employeeSalaries s
JOIN employees e ON e.employeeID = s.employeeID;
In this example, the #employeeSalaries temp table is used to store the results of the subquery that selects employees with a salary greater than 50000. Then, the temp table is joined with the employees table to return the first name, last name, and salary of those employees.
It is also important to note that temp tables can also be created by using SELECT INTO statement. Like this:
SELECT * INTO #employees FROM employees
In conclusion, temp tables are a useful tool for storing and manipulating intermediate results in a SQL query. They are created in the tempdb database and are automatically deleted when the session that created them ends. You can use the CREATE TABLE, INSERT INTO, and SELECT statements to create and manipulate temp tables, and you can use temp tables to store intermediate results in more complex queries.
In addition to the basic usage of temp tables, there are several other features and considerations to keep in mind when working with them.
One important consideration is that temp tables are only accessible to the session that created them. This means that if you have multiple users running queries on the same database, each user will have their own independent copy of any temp tables they create. This can be useful for maintaining data integrity, but it also means that you cannot use temp tables to share data between users.
Another consideration is the scope of temp tables. As mentioned earlier, you can use the "#" symbol to create a local temp table that is only accessible to the current session, or you can use the "##" symbol to create a global temp table that can be accessed by multiple sessions. However, it is worth noting that global temp tables are still only accessible to the current connection, and will not be accessible to other connections using the same login.
You can also create a temporary table with a specific name by using the INTO clause and SELECT statement. Like this:
SELECT * INTO #temp_employees FROM employees
This is useful when you want to persist the data of a specific query to use it later, or when you want to share the data between different sessions.
Temp tables can also be indexed, which can improve query performance when working with large sets of data. You can create indexes on temp tables just like you would on regular tables, using the CREATE INDEX statement. However, it's important to note that indexes on temp tables are also dropped when the table is dropped.
You can also use temp tables as a target for SELECT INTO statement. Like this:
SELECT * INTO #temp_employees FROM employees
This will create a new temp table with the data from the "employees" table.
Finally, it is also important to keep in mind that temp tables are stored in the tempdb database, which is separate from the user databases. This means that temp tables do not participate in database backups, and they are deleted when the server restarts. Therefore, if you need to persist the data in a temp table for a longer period of time, you should consider creating a regular table instead.
In summary, temp tables are a useful tool for storing and manipulating intermediate results in a SQL query. However, it's important to keep in mind that they are only accessible to the session that created them and are stored in the tempdb database, which is separate from the user databases. They can be indexed to improve query performance and can be used as a target for SELECT INTO statement, or created with specific name using INTO clause and SELECT statement. It is also important to consider when a temp table needs to be persisted for a longer period of time, a regular table should be considered instead.
Popular questions
- What is the basic syntax for creating a temp table in SQL?
The basic syntax for creating a temp table in SQL is as follows:
CREATE TABLE #tempTable
(
column1 datatype,
column2 datatype,
...
);
- How can you insert data into a temp table?
You can insert data into a temp table using the INSERT INTO statement:
INSERT INTO #tempTable
VALUES (value1, value2, ...);
- How can you select data from a temp table?
You can select data from a temp table using the SELECT statement:
SELECT * FROM #tempTable;
- What is the difference between a local and global temp table?
A local temp table is created using the "#" symbol and is only accessible to the current session. A global temp table is created using the "##" symbol and can be accessed by multiple sessions. However, it is worth noting that global temp tables are still only accessible to the current connection, and will not be accessible to other connections using the same login.
- How can you persist the data of a temp table for a longer period of time?
If you need to persist the data in a temp table for a longer period of time, you should consider creating a regular table instead of a temp table. Regular tables are stored in user databases and participate in database backups, unlike temp tables which are stored in the tempdb database and are deleted when the server restarts. Additionally, you can use the SELECT INTO statement to create a table with the data from a temp table.
Tag
SQL.
