Reseting Identity Seed

O.k. here's my deal. I have a table who's items get assigned an ID based on
the identity seed. The identity seed is incremented by 1, and the seed
length is 4 . When my program is run, some of these items get moved into
another table for future use and the others are deleted. The items that get
moved are used again the next time the program is run(which is only once a
day). The one items get deleted is based on whether or not they match off t
o
another item read in by the program. Ex. If the data in my table is 1 2 3
4
5 and the program reads in 1 2 4 5, then the 3 is moved to another table and
the 1 2 4 5 get deleted b/c they were matched off. The identity seed is the
n
reset using DBCC CHECKIDENT which works fine, but I need to be able to have
the value it is reset to tied in to something like the date so that it is
unique each time it is reset. This way the next time my program reads in a
3, I need to make sure it does get the same identity seed as the previous 3
that was saved from the first time. I hope you can understand all that and
give me some insight as to how/if this can be done. Thanks.Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"CD" wrote:

> O.k. here's my deal. I have a table who's items get assigned an ID based
on
> the identity seed. The identity seed is incremented by 1, and the seed
> length is 4 . When my program is run, some of these items get moved into
> another table for future use and the others are deleted. The items that g
et
> moved are used again the next time the program is run(which is only once a
> day). The one items get deleted is based on whether or not they match off
to
> another item read in by the program. Ex. If the data in my table is 1 2
3 4
> 5 and the program reads in 1 2 4 5, then the 3 is moved to another table a
nd
> the 1 2 4 5 get deleted b/c they were matched off. The identity seed is t
hen
> reset using DBCC CHECKIDENT which works fine, but I need to be able to hav
e
> the value it is reset to tied in to something like the date so that it is
> unique each time it is reset. This way the next time my program reads in
a
> 3, I need to make sure it does get the same identity seed as the previous
3
> that was saved from the first time. I hope you can understand all that an
d
> give me some insight as to how/if this can be done. Thanks.|||Here's how I read your *requirement* as opposed to what you are
actually asking for. It seems like you have a table and you need to
determine which is the 1st, 2nd, 3rd or Nth row inserted to that table
on any particular day. So add a DATETIME column to the table:
CREATE TABLE YourTable (creation_date DATETIME NOT NULL UNIQUE DEFAULT
CURRENT_TIMESTAMP, ...)
The derive the sequence number like this:
SELECT T1.creation_date, COUNT(*) AS seq
FROM YourTable AS T1
JOIN YourTable AS T2
ON T2.creation_date >= '20050225'
AND T1.creation_date < '20050226'
AND T1.creation_date >= T2.creation_date
GROUP BY T1.creation_date
ORDER BY T1.creation_date
Resetting the seed and relying on IDENTITY to do the same thing is a
really bad idea. IDENTITY sequences can have gaps.
If I've completely misunderstood then the standard advice applies:
Please post DDL, sample data, required results to maximize your chance
of getting a good answer. See:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--sql