Saturday, 31 August 2013

How to set Identity increment ON / OFF in SQL


How to Set IDETITY ON/OFF



In this article I'm trying to explain how to fix identity value how to change the identity value and how to update that value with proper order using Identity column on / off.
 

 
 
Follow the below steps

1) Create a Table with identity column
 
CREATE TABLE #table
(
  
id INT PRIMARY KEY IDENTITY(1,1),
  
name VARCHAR(100),
  
city VARCHAR(100)
)

2) Insert some dummy records to that table.


INSERT INTO #table (name,city)
VALUES('naveen','vijayawada'),('radha','bangalore'),('sanjay','chennai')
 
SELECT * FROM #table


OutPut:


id name  city
1 naveen vijayawada
2 radha  bangalore
3 sanjay chennai


3) Insert duplicate records to that table.


INSERT INTO #table (name,city)
VALUES('naveen','vijayawada'),('radha','bangalore'),('sanjay','chennai')
 
SELECT * FROM #table


output:


id name  city
1 naveen vijayawada
2 radha  bangalore
3 sanjay chennai
4 naveen vijayawada
5 radha  bangalore
6 sanjay chennai


4) By mistakenly I entered some duplicate rows now I delete those records.


DELETE FROM #table WHERE id IN (4,5,6)S
 
ELECT * FROM #table

output


id name  city
1 naveen vijayawada
2 radha  bangalore
3 sanjay chennai


5) Again I added few more records to that table.


INSERT INTO #table (name,city)
VALUES('kaavya','vijayawada'),('kalpana','bangalore')
 
SELECT * FROM #table


output:


id name  city
1 naveen vijayawada
2 radha  bangalore
3 sanjay chennai
7 kaavya vijayawada
8 kalpana Bangalore


6)but the id column is not in proper way for that reason we set identity to ON before inserting and add identity column manually then off the identity column after successfully inserted. Using this we get proper id value.


DELETE FROM #table WHERE id IN (7,8)
 
SELECT * FROM #table

OutPut:


id name  city
1 naveen vijayawada
2 radha  bangalore
3 sanjay chennai


7) Now I set identity on and insert some records then see the result output.


SET IDENTITY_INSERT #table ON
INSERT INTO
#table(id,name,city)VALUES(4,'kaavya','vijayawada'),(5,'kalpana','bangalore')SET IDENTITY_INSERT #table OFF


SELECT
* FROM #table

Now check the output, the id column is in proper way.

OutPut:


id name  city
1 naveen vijayawada
2 radha  bangalore
3 sanjay chennai
4 kaavya vijayawada
5 kalpana bangalore