How to set Identity Increment ON/OFF in SQL
In this artical 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.
Here i provide one simple example go through that example and check how it's working.
How to Set IDETITY ON/OFF
In this artical 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 #tableOutPut:
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)
select * from #tableoutput
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 #tableoutput:
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 #tableOutPut:
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
Follow