In SQL Server, you can use the IDENTITY() function to insert an identity column into a new table.

However, this function is not to be confused with the IDENTITY() property, which is used with the CREATE TABLE and ALTER TABLE statements.

The IDENTITY() function is used only in a SELECT statement with an INTO table clause. So you can use it when transferring data from one table to another, for example.

Syntax

The syntax goes like this:

IDENTITY (data_type [ , seed , increment ] ) AS column_name

The data_type argument specifies the data type of the identity column. Valid data types are any data types of the integer data type category, except for bit and decimal.

The seed is the value that is used for the very first row loaded into the table.

The increment is the incremental value that is added to the identity value of the previous row that was loaded.

The column_name argument provides the name of the identity column that you want to create.

Example 1 – Basic Usage

Here’s a basic code example.

SELECT 
  IDENTITY(int, 101, 10) AS pet_id,
  PetName AS pet_name
INTO BestFriends
FROM Pets;

This selects data from the Pets table and inserts it into a new table called BestFriends. The first column is an identity column that I create with the IDENTITY() function. In this case I set the seed to 101 and the increment to 10. I call that column pet_id.

I call the second column pet_name, and I populate that from the column called PetName.

Here’s what the Pets table looks like:

CREATE TABLE Pets (
    PetId int IDENTITY(1,1) PRIMARY KEY, 
    PetName varchar(255)
    );
INSERT INTO Pets (PetName)
VALUES ('Homer'), ('Felix'), ('Ruff');

SELECT * FROM Pet;

Result:

+---------+-----------+
| PetId   | PetName   |
|---------+-----------|
| 1       | Homer     |
| 2       | Felix     |
| 3       | Ruff      |
+---------+-----------+

And here’s what the BestFriends table looks like after transferring the data:

SELECT * FROM BestFriends;

Result:

+----------+------------+
| pet_id   | pet_name   |
|----------+------------|
| 101      | Homer      |
| 111      | Felix      |
| 121      | Ruff       |
+----------+------------+

So the pet names remain the same, but the identity column contains different values (because I used IDENTITY(int, 101, 10), compared to IDENTITY(1,1) for the Pets table).