How to Insert Values to Identity Column in SQL Server?

How to Insert Values to Identity Column in SQL Server?

The identity field is usually used as a primary key. When you insert a new record into your table, this field automatically assigns an incremented value from the previous entry. Usually, you can't insert your value into this field. In this SQL Server Tutorial, we'll expose the tips for inserting your value into this field. It is simple and easy.

Read More: Basics of SQL Commands

Consider you have the following Customer table.

CREATE TABLE Customer
(
 ID int IDENTITY,
 Name varchar(100),
 Address varchar(200)
)

Now, I am trying to insert a record into the Customer table with an identity field and then I will get the error message as shown below.

INSERT INTO Customer(ID,Name,Address) VALUES(1,'Shakham','Delhi')

Read More: SQL Server Interview Questions and Answers

How to Insert into the Identity Field?

You can insert to the identity field by setting IDENTITY_INSERT ON for a particular table as shown:

Syntax allowing Insertion into the Identity Field

-- Enable IDENTITY_INSERT for the table
SET IDENTITY_INSERT TableName ON;

Example

SET IDENTITY_INSERT Customer ON

Read More - Top 50 DBMS Interview Questions and Answers

How to Disallow Insertion into the Identity Field?

You can also disallow insertion into the identity field by setting IDENTITY_INSERT OFF for a particular table as shown:

Syntax to Disallow Insertion into the Identity Field

-- Disable IDENTITY_INSERT for the table
SET IDENTITY_INSERT TableName OFF;

Example

SET IDENTITY_INSERT Customer OFF

Read More: Different Types of SQL Keys

Insert Value into Identity field

Now, let's see how to insert our values into the identity field ID in the Customer table.

Syntax to Insert Value into the Identity Field

-- Enable IDENTITY_INSERT for the table
SET IDENTITY_INSERT TableName ON;

-- Perform the insert operation, including the identity column
INSERT INTO TableName (IdentityColumn, OtherColumn1, OtherColumn2, ...)
VALUES (ExplicitValue, Value1, Value2, ...);

-- Disable IDENTITY_INSERT for the table
SET IDENTITY_INSERT TableName OFF;

Example

SET IDENTITY_INSERT Customer ON

INSERT INTO Customer(ID,Name,Address) VALUES(3,'Rahul','Noida')
INSERT INTO Customer(ID,Name,Address) VALUES(4,'Rahul','Noida')

SET IDENTITY_INSERT Customer OFF

INSERT INTO Customer(Name,Address) VALUES('Rita','Noida')

After Inserting your values into the identity field don't forget to set IDENTITY_INSERT OFF.

Output

Note

1. Usually, we use this trick when we have deleted some rows from the table and we want the data in a sequence.

2. After Inserting your value into the identity field don't forget to set IDENTITY_INSERT OFF

Considerations when Inserting Values into the Identity Field

• IDENTITY_INSERT Setting: Before inserting explicit values into an identity column, ensure that the IDENTITY_INSERT setting for the table is appropriately enabled.
• Unique Constraint Violations: Each value inserted into the identity column must be unique to maintain data integrity and prevent conflicts with existing data.
• Concurrency: Implement appropriate locking mechanisms or transaction isolation levels to manage concurrency effectively.
• Referential Integrity: Maintain referential integrity with related tables by ensuring that explicitly inserted identity values correspond to valid foreign key references in related tables.
• Index Fragmentation: Inserting explicit values into an identity column can cause index fragmentation, especially in clustered indexes.

Reseed the Identity field

You can also reseed the identity field value. By doing so identity field values will start with a new defined value.

Suppose you want to reseed the Customer table ID field from 3 then the new records will be inserted with ID 4,5,6..and so on.

Syntax to Reseed the Identity field

DBCC CHECKIDENT ('TableName', RESEED, NewSeedValue);

Example

--Reseeding the identity
DBCC checkident (Customer, RESEED, 3)

INSERT INTO Customer(Name,Address) VALUES('Geeta','Noida')

Output

Best Practices for Inserting Values into the Identity Field

• Avoid Explicit Insertion: Explicitly inserting values into an identity column can lead to data integrity issues and conflicts with existing data.
• Use Set Identity Insert: If you must insert explicit values into an identity column, use the SET IDENTITY_INSERT statement to enable explicit identity value insertion temporarily.
• Maintain Unique Constraints: Ensure that the values inserted into the identity column do not violate any unique constraints defined on the column.
• Validate Identity Seed: Ensure that the new values being inserted do not conflict with or overlap existing identity values.

What do you think?

I hope you will enjoy the tips while playing with SQL Server. I would like to have feedback from my blog readers. If you want to gain a practical understanding, you can enroll in our SQL Server Course.

Do you Know?

.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.

Therefore, if you want to upskill yourselves and stand out from others consider our following training programs on .NET.

• .NET Developer Training With Certification
• ASP.NET Core Certification Training
• ASP.NET Core Course
• .NET Solution Architect Certification Training
• Full-Stack .NET Developer Certification Training Program
• Advanced Full-Stack .NET Developer Certification Training

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE