reset the identity - 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

General SQL Server Forums

New to SQL Server Programming

reset the identity

Author

Topic

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-25 : 00:45:51

Hi,

CREATE TABLE #temp
(
ALLOTMENT_ID int identity(1,1)
,ALLOTMENT_FLIGHT_ID int
,ALLOTMENT_CLASS int
,ALLOTMENT_SEATS int
)

INSERT INTO #temp VALUES(1,1,10)
INSERT INTO #temp VALUES(1,1,10)
INSERT INTO #temp VALUES(2,2,20)
INSERT INTO #temp VALUES(2,2,20)
INSERT INTO #temp VALUES(3,3,30)
INSERT INTO #temp VALUES(4,4,40)

delete #temp
where ALLOTMENT_FLIGHT_ID=1

before deleting 2 records identity column values 1,2,3,4,5,6

after deleting records i need to reset identity column values 3,4,5,6 to 1,2,3,4

deletion is done through a storedprocedure sp_del_FLIGHT with a input parameter ALLOTMENT_FLIGHT_ID

could you assists me

Thanks

--Ranjit

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-25 : 01:21:07

My first thought is "Why!?"
Why do you want to do this?

N 56°04'39.26"
E 12°55'05.63"

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-25 : 05:04:09

I think you are asking us for to help in degrading the performance of your database

Cheers
MIK

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-25 : 08:45:24

If the serial number matters do it in front end application

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-02-26 : 09:52:58

you can still retrieve records in order you want without doing this.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources