How can i reset RESET IDENTIY SEED for this case ?

Question

How can i reset RESET IDENTIY SEED for this case ?

asi0ng

Valued Member

Points: 68
More actions
December 11, 2002 at 8:41 pm

#80550

Hi,
I'm a newbie working with SQL Server (2000) and need some assistance for my problem.
I have 2 table use for invoice transaction,
for fields "SeqNo" at "InvoiceDetails" table,
I'm use IDENTITY for auto increment number.
My Purpose is
use SQL help me to increment the number for my Sequence No
everytime I create new TransNo,
,no need other external function/procedure from client side.
Problem : How to Reset identity Seed at "SeqNo" field everytime i change TransNo ?
Have any idea ?
Invoices
--------
TransNo Date Customer GrandTotal
------- -------- ------- ----------
00011/2/1999 Mr A45
00021/3/1999 Mr B56
00031/5/1999 Mr C48
InvoiceDetails
---------------
Primary_Keys :
1. TransNochar(4)
2. SeqNoInteger --> Identity(1,1)
3. ProdCodeChar(3)
(auto)
TransNo SeqNo ProdCode Qty Price Total
------- ----- -------- --- ----- -----
00011A11010
00012B21020
00013B11515
00021C16 6
00022B51050
00031A21224
00032B21224
Note : SeqNo = auto increment number (and auto reset everytime change TransNo)
Thx,
Jonny

Viewing 4 posts - 1 through 3 (of 3 total)

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

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 1

The IDENTITY feature isn't intended to be continually reset in the fashion you describe. You need to create either a stored proc to maintain and reset your seed values. You might create a trigger to reset your number to 0, then your stored proc will only ever need to increment the number.

Whatever you do, I think you should remove the IDENTITY value.

If you insist on do this, the DBCC CHECKIDENT() function is how you reset the seed values.

gljjr SSChampion Points: 10080 More actions · Answer 2

As Don stated this is not a good use of an identity column.

The way I have done this in the past is to create a temp table and put the InvoiceDetails for one invoice into the temp table and then copy them to the actual table. This of course would always be done in an SP.

Gary Johnson

DBA

Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

asi0ng Valued Member Points: 68 More actions · Answer 3

Thx u everyone who responded, i think i wouldn't use IDENTITY SEED for this case!