Creating a Temp Table with an Identity in SQL

In SQL Server, temporary tables are used for holding intermediate results or performing operations during a session or stored procedure. One useful feature of tables is the ability to have an identity column that auto-increments values as rows are inserted. This is particularly useful for generating unique IDs or primary key values.

In this article, we will explore how to create a temporary table with an identity column, how it behaves, and the various ways to work with it.

What is an Identity Column?

An identity column in SQL Server is a column that automatically generates numeric values for each row that is inserted into the table. These values typically start from a specified seed value and increment by a defined increment.

The basic syntax for defining an identity column is as follows:

column_name INT IDENTITY(seed, increment)

Explanation:

• Seed: The starting value for the identity column.
• Increment: The value by which the identity column increases for each new row.

What is a Temporary Table?

A temporary table in SQL Server is a table that exists temporarily during the session or the life of a stored procedure. These tables are useful for storing intermediate data during the execution of queries or procedures. Temporary tables are of two types:

1. Local Temporary Tables (#): These tables are visible only to the session that created them and are automatically dropped when the session ends.
2. Global Temporary Tables (##): These tables are visible to all sessions and are dropped when the last session using them is closed.

Creating a Temporary Table with an Identity Column

To create a temporary table with an identity column, we can use the same syntax as a regular table, but specify the # or ## prefix to create a temporary table. The identity column will automatically generate incremental values for each row.

Example 1: Creating a Local Temporary Table with an Identity Column

Here’s an example of creating a local temporary table with an identity column:

Query:

Let's create a local temporary table named #TempEmployee with an identity column that automatically increments and populate this table with sample employee data and retrieve the information stored in the table. Afterward, drop the temporary table to remove it from the session.

-- Create a local temporary table with an identity column
CREATE TABLE #TempEmployee (
    ID INT IDENTITY(1, 1),  -- Identity column starting from 1 and incrementing by 1
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

-- Insert sample data into the temporary table
INSERT INTO #TempEmployee (FirstName, LastName)
VALUES 
    ('John', 'Doe'),
    ('Jane', 'Smith'),
    ('Mark', 'Taylor');

-- Select the data from the temporary table
SELECT * FROM #TempEmployee;

-- Drop the temporary table after use
DROP TABLE #TempEmployee;

Output:

ID    | FirstName  | LastName
------|------------|-----------
1     | John       | Doe
2     | Jane       | Smith
3     | Mark       | Taylor

Explanation:

• The table #TempEmployee is created with an identity column ID. This column automatically starts from 1 and increments by 1 for each row inserted.
• We insert sample data into the FirstName and LastName columns, and SQL Server automatically assigns a unique value to the ID column.
• After the data is inserted, we select all rows from the temporary table to view the results.
• Finally, the table is dropped using DROP TABLE after it is no longer needed.

Example 2: Creating a Global Temporary Table with an Identity Column

In some cases, we may need a global temporary table that can be accessed across different sessions. Here’s how we can create one with an identity column:0

Query:

-- Create a global temporary table with an identity column
CREATE TABLE ##GlobalTempEmployee (
    ID INT IDENTITY(1000, 10),  -- Identity column starting from 1000 and incrementing by 10
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

-- Insert sample data into the global temporary table
INSERT INTO ##GlobalTempEmployee (FirstName, LastName)
VALUES 
    ('Alice', 'Brown'),
    ('Bob', 'White'),
    ('Charlie', 'Green');

-- Select the data from the global temporary table
SELECT * FROM ##GlobalTempEmployee;

-- Drop the global temporary table after use
DROP TABLE ##GlobalTempEmployee;

Output:

ID    | FirstName  | LastName
------|------------|-----------
1000  | Alice      | Brown
1010  | Bob        | White
1020  | Charlie    | Green

Explanation:

• The global temporary table ##GlobalTempEmployee is created with an identity column ID. The identity values for this table start from 1000 and increment by 10 for each new row.
• Sample data is inserted, and SQL Server automatically generates the ID values.
• After performing the necessary operations, the global temporary table is dropped.

Important Considerations When Working with Temporary Tables and Identity Columns

1. Scope of Temporary Tables:

• Local Temporary Tables (#TempTable) are available only within the session in which they are created. They are dropped automatically when the session ends.
• Global Temporary Tables (##TempTable) are accessible by all sessions and are dropped when the last session referencing them is closed.

2. Identity Column Behavior:

• The identity column in a temporary table behaves the same as in a regular table, automatically generating incremental values for new rows.
• The identity values are session-specific in the case of local temporary tables. This means that different sessions can have different identity values.

3. Reseeding the Identity Column:

• If you want to change the starting value of the identity column after some rows have been inserted, you can use the DBCC CHECKIDENT command to reseed the identity.

Conclusion

Creating a temporary table with an identity column in SQL Server is a simple process that allows you to store and manipulate data with automatically incrementing IDs within the scope of a session. These tables can be used for intermediate data storage during query execution or within stored procedures. By using identity columns in temporary tables, you can easily generate unique IDs for each row without manually specifying them.

While temporary tables are useful in many scenarios, it’s important to understand their scope and limitations. When working with temporary tables and identity columns, ensure that you choose the appropriate type (local or global) and understand how identity reseeding works.