how to write query for identity

Question

i hav a table having identity from 1 to 20

want  after 20, identity will start from  31 

what is the query for this

Answer 1

Res BOL http://msdn.microsoft.com/en-us/library/ms176057.aspx

C. Forcing the current identity value to a new value

The following example forces the current identity value in the BusinessEntityID column in the Employee table to a value of 300. Because the table has existing rows, the next row inserted will use 301 as the value, that is, the new current increment value defined for the column value plus 1.

Transact-SQL

USE AdventureWorks2012;
GO
DBCC CHECKIDENT ("Person.AddressType", RESEED, 10);
GO

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Set Identity_Insert ON  and then insert the value with your identity value. Then Set Identity_Insert OFF

create table #b
( id integer identity(1,1),
name varchar(20))

insert into #b(name) values('alok')

set identity_insert #b on 
insert into #b(id,name) values(10,'alok')
set identity_insert #b off

insert into #b(name) values('alok')
select * FROM #b

ind name
1 alok
10 alok
11 alok

Answer 3

To do that  you need to modify every procedure where  you insert the data. And what is about UNIQUENESS ?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

Check for identity using @@Identity after insert.

if @@Identity is 20 then execute following command

DBCC CHECKIDENT (yourtable, reseed, 31)

You can refer more about reset idetity from

http://blog.sqlauthority.com/2007/03/15/sql-server-dbcc-reseed-table-identity-value-reset-table-identity/