Reset identities and using DBCC CheckIdent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Reset identities and using DBCC CheckIdent

Author

Topic

wiltech
Yak Posting Veteran

51 Posts

Posted - 2003-07-11 : 17:41:06

We are using DTS to transfer the data from one db to another. During the course of the design of the DTS package it will be ran serveral time, thus the first step in it is to delete all of the existing data from the new database, and then reset the identities.

I am using the following t-sql to reset the identities:

 DECLARE @TableName varchar(255)
DECLARE output_curs CURSOR FOR
   select  d.name as [Table]
   from sysobjects d
   where d.type = 'U'
      and d.name not like 'dt%'
   ORDER BY d.Name

OPEN output_curs
   FETCH NEXT FROM output_curs INTO @TableName WHILE (@@FETCH_STATUS = 0) BEGIN
      PRINT @TableName 
      /* Reset the identity */
      DBCC CHECKIDENT(@TableName, RESEED, 0)
      PRINT ''
      FETCH NEXT FROM output_curs INTO @TableName
   END
CLOSE output_curs
DEALLOCATE output_curs

This works and the next entry into the table will have and identity of 1, except if the table hasn't had any data add to it, then the first row will have a 0. This has been documented in BOL

Is there a way to make sure that the first row in the table has it's identity set to 1? or am I just being too picky about this?

Thanks
Tony W

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-11 : 18:06:42

Why does it matter if the identity value is 0 or 1? Identity values are meaningless usually.

You could check if you had data in that table originally and set the identity value to 1 instead of 0.

Tara

wiltech
Yak Posting Veteran

51 Posts

Posted - 2003-07-11 : 18:10:40

quote:

Why does it matter if the identity value is 0 or 1? Identity values are meaningless usually.

Agreed, I would just like them to be consistant.

quote:

You could check if you had data in that table originally and set the identity value to 1 instead of 0.

Tara

Is there a way to do this after the data has been deleted? i.e. via a system table

The tables have to be deleted in a specific order to avoid violating foreign key constraints.

Thanks
Tony W

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-11 : 18:21:02

quote:

Is there a way to do this after the data has been deleted? i.e. via a system table

Not that I am aware of. You need to store that information somewhere, like in a table. Before all of the deletes, run COUNT(*) on all of the tables and store that in another table. Then when you go to reset the identity, do a check on this new table.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-11 : 18:38:32

Just thought of something else...

Instead of deleting the data, you could drop the tables, then recreate them with identity set to 1. Then these tables would never have had data in them so the first record inserted would be identity value 1.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-11 : 19:13:59

DECLARE @TableName varchar(255), @sql varchar(1000)
select d.name into #a
from sysobjects d
where d.type = 'U'
and d.name not like 'dt%'
select @TableName = ''
while @TableName < (select max(name) from #a)
being
select @TableName = min(name) from #a where name > @TableName
select @sql = 'truncate [' + @TableName + ']'
exec (@sql)
end

truncate will set to the original seed (I think).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-11 : 19:20:04

Just tested Nigel's approach and truncate does set identity to the original seed. So go with truncate instead of delete.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-11 : 19:22:38

While I'm pointing out the cursor I might add "are you sure you want to use dts for this?" (whatever you are doing).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources