SQL Temp Table: Temporarily Create a Table in a Stored Procedure

Udemy Editor

If you’ve worked with SQL Server before, you probably know the concept of a physical table. You create a table of columns where you store your data, and you use this table to query for specific information in your stored procedures. SQL Server also offers you a way to create temporary tables. These temporary tables are created the same way, but you create and destroy them on-the-fly. These temporary tables are beneficial when you need to loop through data such as using a cursor or need to remove duplicate records from a larger record set.

New to SQL Server programming? Learn how to create queries, reports and stored procedures at Udemy.com.

How to Design a Table

Before you create a temporary table, you should first know how to design a table whether it’s temporary or permanent. SQL tables are made up of columns. Each record is a row. A SQL table is a more complex and useful form of a common spreadsheet. A spreadsheet is a good way to compare a table’s result set even though it’s technically inaccurate. However, it will help you understand the concept of a row and a column.

When you design your physical tables, you should use the same concepts when designing your temporary table. Even though temp tables are created on-the-fly, you should still consider column data types and indexes to speed up your queries and to ensure that the right data is stored in the temp table.

Since temp tables are created using the same “create table” as other tables, you need to consider the data you will store in the table. You should also set a primary key when you create the table and use indexes if you plan to use the stored procedure or query often.

Using the CREATE TABLE Procedure to Create a Temp Table

Most SQL Server table designers work with SQL Server Management Studio, which lets you use a visual tool for creating tables. However, if you ever want to create a table in a procedure or using code, you need the CREATE TABLE command. Actually, the visual tool in Management Studio uses the CREATE TABLE command, but it just shields the more complex commands from view and makes it easier to create a table.

To indicate to the SQL engine that you want a temp table, you use the “#” character. Using this character in front of any unique table name will create a temp table that you can use for the life of your stored procedure.

The following code is an example of a CREATE TABLE command for a customer temp table:

CREATE TABLE #Customer_Temp
(
CustomerId int NOT NULL,
FirstName varchar(50),
LastName varchar(50)
)

In the above code, a temp table named “Customer_Temp” is created. There are three columns: the customer id, and first and last name. The first column is an integer and the next two columns allow you to store strings.

At this point, only the table structure is created. Most temp tables are used for data manipulation from other tables. For instance, maybe you want to identify multiple customer accounts in a customer table. You could retrieve the duplicate accounts and store them in a temp table. The temp table would then allow you to count how many duplicate values are in your table. You can use just the count or even flag the duplicate accounts in the original customer table.

The following code gets a list of duplicate customer accounts based on the customer’s last name:

insert into #customer_temp (firstname, lastname, customerId)
select firstname, lastname, customerId from customer group by firstname, lastname, customerId having ( count(lastname) > 1 )

Of course, most business logic requires another column to detect duplicate records, because more than one customer can have the same last name. However, this is just a simple example to help you understand temp tables and finding duplicate records.

In the code above, any customer that has a duplicate record based on a record that has the same last name is inserted into the temp table. You can assume that the customerId column only allows unique records, so you get a unique Id using this column from the main customer table.

This query could result in thousands of records, so if you plan to do any linking between tables, you should set a primary key and index on the temp table to speed up the queries. This is accomplished using the ALTER TABLE phrase.

Learn how to work with the SQL language at Udemy.com.

Change Table Structure Using the ALTER TABLE Command

If you use SQL to create your tables, you should be familiar with the ALTER TABLE command, because it changes the table structure after you’ve already created it. In the above CREATE TABLE command, the table structure was created and it’s a usable structure, but if you have thousands of records and need to link to other tables in your database, it can affect performance greatly if you have no indexes or a primary key. You can dynamically create these two components on a SQL temp table.

First, you create a primary key on a column that must be unique. This can be difficult on a temp table that contains dynamically inserted records. The query used above uses logic that says the records inserted should be unique, but SQL will throw an error if you try to make a primary key on a temp table that has the same value for multiple records. Therefore, when you create a primary key dynamically, you should be sure that your temp table only has unique values for the primary key column.

The following code creates a primary key on the CustomerId column:

ALTER TABLE #Customer_Temp ADD PRIMARY KEY (CustomerId)

Simple enough. The ALTER TABLE is called on the Customer_Temp table and the primary key is added on the CustomerId column. Again, if there is any duplicate value in this CustomerId column, the ALTER TABLE statement will fail. Additionally, primary keys cannot contain NULL values. If you notice in the CREATE TABLE statement, the “not null” specification is made on the column, so you will get an error when you try to insert any null value prior to setting the primary key in this example. However, if you don’t specify NOT NULL in your temp table’s create statement and then attempt to create a primary key on that same column, SQL will return an error if any NULL values exists.

Create an Index on Your Temp Table Using CREATE INDEX

If you’re only doing some queries on the temp table’s primary key, then you probably don’t need an additional index. A primary key is a clustered index, so creating one on your table automatically sets an index that will speed up your temp table queries.

Suppose you want to link the last name column from your temp table to your main customer table’s last name column. This would greatly slow down your queries, because the temp table does not have an index on the last name column. Therefore, you probably want to set an index on the last name column.

The following code creates an index on the last name column in your temp table:

CREATE INDEX tempCustomerIndex ON # Customer_Temp (LastName)

The code above creates a non-unique index on the temp table’s LastName column. A non-unique index allows nulls and the same value in the column. You can also use the UNIQUE keyword in front of the INDEX keyword to specify that the column’s values must be unique. But, again, you must make sure that the values in the column are unique or the SQL statement will fail and give you an error.

Querying a SQL Server Temp Table for Results

With the temp table set up, you can now perform queries on it. Query structure for a temp table is the same as a regular table. To get a list of records, for instance, from the temp customer table, use the following query example:

select * from #Customer_Temp order by LastName

The above statement gets all records inserted into your temp table and orders it by the last name column.

You might want to verify that you retrieved all of the right records and that your temp table contains the right data. Most significant queries require you to sample some data to verify accuracy. With this example, you can link the customer Id columns together. You would then verify that the customer Id and information in your temp table is the same as the information in your main customer table. It ensures that you edit the right records. For instance, the following code links the temp and main table and displays a data set that you can use to sample the data for accuracy:

select * from #Customer_Temp ctemp join Customer c on ctemp.CustomerId = c.CustomerId order by c.LastName

In the above statement, the two tables are linked, but notice the use of aliases. The tables are aliased using “ctemp” and “c.” The ctemp alias points to the temp table and the c alias points to the main customer table. Both these tables have the same columns, so you need to alias them to use them in your “where” clause. There is no where clause in this query, but there is an “order by” clause, so you need to specify whether you want to use the temp table’s last name column or the main Customer table’s column. In this example, “c.LastName” refers to the Customer table’s last name column.

You can also update records based on the two tables. For instance, suppose you want to update a “duplicate” column for each record in the main customer table where it’s copied over to the temp table as a duplicate. Because our temp table contains a list of customers found to be duplicates, you can use this table data to update the main customer table accurately.

The following code updates a customer table based on temp table data:

update customer set duplicate = 1 where customerId in (select customerId in #Customer_Temp)

In the above query, the customer table is assumed to have a “duplicate” column that takes either an integer or a bit. The “1” is “true” in this example. Because we wanted to know what customers were a duplicate, the original insert query only inserted customers that appeared in the table more than once based on the last name. Normally, you would use some other factor such as social security number, telephone number or email address. This example uses the last name for simplicity, but you will likely have more than one customer with the same last name.

The business logic for the above query is “if the customer exists in the temp table that contains duplicate record Ids, then flag the main customer as a duplicate record.” If your temp table contained thousands of records, this query could slow down your production database to a crawl. However, with the indexes and primary key set up, the query should not run for long and it should not interrupt regular production such as a front-end database or an internal customer service application.

This article contains just a sample of how to use a temp table, how to create one and how to alter it to speed up queries. The example using duplicate records and flags is something you will probably run into when you work with an enterprise database SQL Server. Duplicate records can cause data integrity issues, so it’s likely you’ll need to identify duplicate records to help streamline your system.

As always, be very careful before you run an update statement on your production data. Always sample the data first and run the query only when you are sure the update query is accurate. You should back up your database table before you run the query as well in case you need to roll back any changes. If any mistakes are made, you can transfer data to the original before the update query so no data corruption occurs.

If you need to know more about SQL and want to learn more complex queries, look at Udemy.com for an online SQL course.