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),
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
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')
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
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')
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