Setup a identity value in an existing table

[camaleonCHILE]

Hi, I've a table with a IDENTITY column, when I execute "SELECT IDENT_CURRENT('mytable')", this statement returns 161. Now I wanna change this value (for example,to 100). I'm sure you masters have an answer for this.

Thanks in advance

Regards

 

[SQLDenis]

lookup DBCC CHECKINDENT in BOL

DBCC CHECKIDENT (mytable, RESEED, 100)


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Do you actually want to update this value to 100 from 161?

if so take a look at this

create table checkidenttable ( id int identity , datevalue datetime)
insert into checkidenttable
select getdate() union all
select getdate() union all
select getdate() union all
select getdate() 

select * from checkidenttable -- 4 rows (1,2,3,4)

delete from checkidenttable where id =2  -- 3 rows (1,3,4)

set identity_insert checkidenttable on
insert into checkidenttable(id,datevalue)  --insert 2
select 2,datevalue from checkidenttable
where id =4
set identity_insert checkidenttable off

delete checkidenttable   --delete 4
where id = 4  

select * from checkidenttable -- 4 rows

also make sure that the value 100 doesn't exist already, SQL won't do it anyway you will get 0 rows affected

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/