Restarting numbering when deleting tables

Question

Post reply

Restarting numbering when deleting tables

FelixG

SSCrazy

Points: 2550
More actions
April 3, 2006 at 6:01 pm

#152882

Is there some way of resetting identity generated number when deleting all records of the table.
In my particular case I have a table containing daily invoices, where invoice number is an integer identity field (primary key).
When the working day finishes I move all invoices to a historic table (the primary key is WorkingDate+InvoiceNumber) and delete the daily table.
I would like to start every day with the number 1 for the identity field "InvoiceNumber". I could drop an recreate the invoices table, but there must exist any other better solution.

Thanks in advance,
Felix

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

If I understand this right you may be looking for..

dbcc checkident('tblName', reseed, 1)

**ASCII stupid question, get a stupid ANSI !!!**

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Did you bother to open BOL on topic "IDENTITY (property)"?

_____________
Code for TallyGenerator

FelixG SSCrazy Points: 2550 More actions · Answer 3

I'm sorry if I disturbed You. It was not my intention at all. In any case you replied because you wanted to.

Thanks anyway

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

Not that I'm clamouring for world peace or anything but why is a basic level of amicability so hard to come by ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

Nick Waddell SSC Enthusiast Points: 101 More actions · Answer 5

Nick Waddell

SSC Enthusiast

Points: 101

April 3, 2006 at 7:35 pm

#630554

Truncating the table will reset the identity number

Paul Mu SSCertifiable Points: 5547 More actions · Answer 6

One should always take comments like this to be instructive, which is what it was meant to be! In any case, BOL should be your first point of call - it may even save you time by getting answers to your questions immediately.

sushila SSC-Dedicated Points: 35293 More actions · Answer 7

And what if it were a simultaneous process - what if one was scouring BOL while waiting for an answer from one of the many experts here...?!?!

**ASCII stupid question, get a stupid ANSI !!!**

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 8

Neither identity or max(n)+1 will prevent gaps. They both assume the highest value in the table is also the number of values in the table.

Instead if you want to "issue" invoice numbers to branches, simulate the old paper process. Write your table with a loop or select and then populate it with the numbers you want to use. When you issue one to a branch (or a block as mentioned by Mr. Celko), mark it as issued to that branch, or just issued, depending on your needs.

noeld SSC Guru Points: 96590 More actions · Answer 9

If you are posting a solution that does not works with MS SQL Server in an MS SQL Server Forum what sense does that makes???

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 10

I may not have been in this field as long as you have but I don't consider myself a newbie

Your "standard" SQL 92 is not by any means the Gospel either. Can you tell how many vendors run their own extensions? ANSWER: ALL

When posting a solution that runs somewhere else than what the user requested it is absolutely of no use for the poster regardless of maintainability

* Noel

Jeff Gray SSChampion Points: 10667 More actions · Answer 11

I would like to point out that this was orginally supposed to be a flamewar over being nice about suggesting that someone read the directions before asking questions.

Now this.

We finally get two for the price of one, and I didn't bring any popcorn.

sushila SSC-Dedicated Points: 35293 More actions · Answer 12

well guess what...I brought the popcorn but was too rivetted to have any!!

**ASCII stupid question, get a stupid ANSI !!!**

Andreas AJV SSC Eights! Points: 800 More actions · Answer 13

Write a sp "EndOfDay" or update the current (I believe you use one, to transfer the data into history table) to do the following:

-After tranfser and delete the rows in the current table

-Alter the table structure

-Delete the Invoice ID column

-Insert the column again....

------------
When you 've got a hammer, everything starts to look like a nail...