Reset identity when value in other column changes? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 Development (2000)

Reset identity when value in other column changes?

Author

Topic

redlam
Starting Member

7 Posts

Posted - 2002-07-31 : 19:23:21

Is it possible to 'reset' the value of an identity column based on values in another column? Here's an example of what I'd like to do:

AutoID/TeamID
1 0
2 0
3 0
1 1
2 1
3 1
1 2
2 2
3 2
1 3

Right now, the identity column 'AutoID' is seeded at 1 and incremented at 1. Is it possible to have this field 'reset' itself to 1 everytime the data in the 'TeamID' column changes?

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-07-31 : 23:57:01

I think using an identity column for what you are trying to accomplish is an implementation nightmare. Trying to manage DBCC CHECKIDENT inside what would have to be a trigger would seem like to much trouble IMHO.

try the following (untested, but should be close in theory)

create proc sprInsertTeamIDAndNextID
@TeamID int 'team that is being inserted
as
set nocount on
declare @NextVal
select @NextVal = isnull(max(AutoID), 0) + 1 from MyTable where TeamID = @TeamID

insert into MyTable
values (@TeamID, @NextVal)

the isnull() + 1 will ensure that any new teams inserted will start with a value of 1. If you are running SQLServer 2K you could do this as a UDF instead of a stored procedure.

hth,
Justin

redlam
Starting Member

7 Posts

Posted - 2002-08-01 : 14:09:53

Thanks Justin! UDF sounds like a plausible idea. I'll give it a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources