Insert Values in SQL server Identity Column

Insert Values in SQL server Identity Column

Identity Column Values is identical in SQL. While creating this column auto-increment property can be set which creates numeric sequence.

I have created a table named as 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 make sure 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 above error when am trying to insert any value into 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, the next highest number would be inserted, not the original value.  Let's check out the example below to show how we can resolve this issue.

 Insert Value to Identity field

Now, let’s see how to insert our own values to identity field ID with in 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

Usually, we 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 to identity field don't forget to set IDENTITY_INSERT OFF

  

comments powered by Disqus