resetting Identity value - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

resetting Identity value

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-05-02 : 03:17:30

I am trying to reset an IDENTITY RowNumber
back to 1 in a query when the Person's Name is the same. The following query
can be used agains the pubs database in SQL Server.

I am trying to figure out where to place the section for the DBCC CHECKIDENT item in order to reset the RowNumber back to 1 when ever the lname column is the same. For example if the pub database had duplicate records for a given employee the RowNumber would reset back to 1.

CREATE TABLE #RowNumber (
RowNumber int IDENTITY (1, 1),
emp_id char(9) )
--DECLARE @seed int
--SET @seed = 1
--DBCC CHECKIDENT ('#RowNumber', RESEED , @seed) WITH NO_INFOMSGS

INSERT #RowNumber (emp_id)
SELECT emp_id
FROM employee
ORDER BY lname
SELECT RowNumber, e.emp_id, lname, fname, job_id
FROM #RowNumber r JOIN employee e
ON r.emp_id = e.emp_id

ORDER BY RowNumber
DROP TABLE #RowNumber

Ganesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-02 : 03:33:00

Why do you want to do that?