Insert Values in Identity Column in SQL Server

Identity Column Values are identical in SQL. When creating such a column the auto-increment property can be set that creates a numeric sequence.

I created a table named Customer and for the CustomerID column, "IDENTITY(1,1)" is specified. This means that, as each row is inserted into the table, SQL Server will automatically increment this value by 1 starting with the number 1.

CREATE TABLE Customer

(

ID int IDENTITY(1,1),

Name varchar(100),

Address varchar(200)

)

This is a point of interest because as we begin to write our INSERT statements, we will need to ensure we do not include this column because SQL Server will manage it for us.

Insert INTO Customer (NAME,ADDRESS) Values('Puja','Hyderabad')

Insert INTO Customer (NAME,ADDRESS) Values('Xyz','Hyderabad')

Insert INTO Customer (NAME,ADDRESS) Values('ABC','Hyderabad')

Select * from customer

Result:

So now let' see what happens if we want to insert a record with a specific CustomerID.

Insert INTO Customer (ID,NAME,ADDRESS) Values(4,'SSS','Hyderabad')

Result:

SQL Server has thrown the preceding error when I attempt to insert a value into the ID column. For example, let's say a customer was deleted by mistake and you want to retain their original CustomerID. What would you do? If you inserted the record like we have in the examples above then the next highest number will be inserted, not the original value. Let's check out the example below to show how to solve this issue.

Insert Value to Identity field

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

SET IDENTITY_INSERT Customer ON

INSERT INTO Customer(ID,Name,Address) VALUES(4,'SSS','Delhi')

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

SET IDENTITY_INSERT Customer OFF

INSERT INTO Customer(Name,Address) VALUES('Madhu','Bangalore')

Result:

Note

We usually use this trick when we have deleted some rows from the table and we want the data in a sequence.

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

Insert Values in Identity Column in SQL Server

Reactions

2 Comments