Creating And Inserting Data Into A Temporary Table In SQL Server
A temporary table, or temp table, is a user created table that exists for the sole purpose of storing a subset of data from one or more physical tables. Temp tables can be used to store large amounts of data that would otherwise require numerous queries to repeatedly filter that data. Temporary tables exist only while the connection that created them is active or until they are manually dropped by the user or procedure and reside within the tempdb
system database.
It is also worth noting that a standard temporary table, which is what I will primarily be discussing in this post, is only accessible to the connection that created it. However, you can also create global temporary tables which are available to any connection. I will briefly discuss this concept at the end of the article.
The steps outlined below will guide you through the process of creating a physical table to store some data and then creating a temp table which will then be populated by the data from the physical table.
The first step is to create a physical database table and populate it with data. The script outlined below will create a table called employee
. The table will contain an employee ID column which will be an auto incremented value and act as the PRIMARY KEY
. The table will also include the Last Name, First Name, Hire Date and Job Title.
CREATE TABLE employee
(
emp_id INT IDENTITY PRIMARY KEY,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(30) NOT NULL,
hire_date DATETIME NOT NULL,
job_title VARCHAR(50) NOT NULL
)
The next step is to populate the newly created employee
table with some data that we can use. The following script will uses the INSERT INTO
function call to do this.
INSERT INTO employee
VALUES ('Smith', 'James', '3/1/2016', 'Staff Accountant'),
('Williams', 'Roberta', '2/7/2004', 'Sr. Software Engineer'),
('Weinberg', 'Jeff', '1/2/2007', 'Human Resource Manger'),
('Franklin', 'Victoria', '7/2/2010', 'Operations Manager'),
('Armstrong', 'Williams', '11/14/2012', 'Database Administrator'),
('Cromley', 'Eric', '9/9/2009', 'Recruting Manager'),
('Richardson','John', '2/11/2007', 'Safety Clerk'),
('Horton', 'Michelle','6/12/2009','Accounting Manager'),
('Washington','Mark','8/19/2014', 'HelpDesk Technician')
Now that the physical table has been created and populated, you can easily query the table.
SELECT * FROM employee
The following screenshot illustrates the output.
There are two ways to go about creating and populating a temp table.
The first, and probably simplest method for doing so, is to SELECT
the data INTO
the temp table. This essentially creates the temp table on the fly. The example below will create a temporary table and insert the last_name
, first_name
, hire_date
and job_title
of all employees in the physical employee
table with a hire_date
that is greater than 1/1/2010.
SELECT last_name, first_name, hire_date, job_title
INTO #tmp_employees
FROM dbo.employee
WHERE hire_date > '1/1/2010'
You can query the temp table just like any physical table.
SELECT * from #tmp_employees
The output should appear as follows:
As previously discussed, there are two way to remove the temporary table. The first is to close the connection to the database that created the temp table. The other is to execute the following command.
DROP TABLE #tmp_employees
This method is more useful in most practical applications as you can utilize the drop command along with a validation check when creating temp tables in stored procedures to verify whether the temp table already exists or not and drop it prior to running the procedure. An example of this type of logic can be seen below.
IF OBJECT_ID('tempdb..#tmp_employees') IS NOT NULL
DROP TABLE #tmp_employees
The second method for creating and populating a temp table involves first creating the temp table and then using the INSERT INTO
command to populate the temp table. These steps are similar to the steps used to create and populate the physical table above.
You’ll notice in the following script that when creating the temp table you will need to assign a datatype to each column that you are creating. Best practice is to match the datatype of the physical table or tables from which the data will be retrieved. This will prevent potential truncate errors from occurring when populating the temp table.
CREATE TABLE #tmp_employees
(
last_name VARCHAR(30),
first_name VARCHAR(30),
hire_date DATETIME,
job_title VARCHAR(50)
)
INSERT INTO #tmp_employees
SELECT last_name,
first_name,
hire_date,
job_title
FROM dbo.employee
WHERE hire_date < '1/1/2010'
After creating the table the script uses the INSERT INTO
command to populate #tmp_employees
with the last_name
, first_name
, hire_date
and job_title
of all employees from the physical employee
table who have a hire_date
less than 1/1/2010.
Again, you can query the data using the same select statement provided above.
As mentioned previously, these types of temp tables are only accessible to the connection that created them. In order to create a globally accessible temp table all you need to do is include double hash marks in front of the table name. Global temp tables also expire when the user or procedure that created them is no longer active. However, any database user can access a global temp table while it exists.
Using the same logic from the first example, the following script creates a global temporary table.
SELECT last_name, first_name, hire_date, job_title
INTO ##tmp_employees
FROM dbo.employee
WHERE hire_date > '1/1/2010'
Just like normal temporary and physical tables, the global temp table can be queried in the same manner.
SELECT * FROM ##tmp_employees
Whether your are new to SQL or new to the concept of temporary tables, I hope the steps outlined above have provided some valuable insight into how to create and manage temporary tables within SQL Server.