Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

How do I auto increment a field that is not the identity

I have a field in a table that I would like to increment by 1 everytime a new record is added.  Preferably this would be on the table but I could include it in the stored procedure.  Also, I would like to start with the number 4,000 and increment from there.  Any ideas?
0
kkirt1
Asked:
kkirt1
1 Solution
 
Anthony PerkinsCommented:
Define it as an Identity column, with an Identity Seed of 4000 and an Identity Increment of 1

This creates a new table:

CREATE TABLE Table1 (
     ID int IDENTITY (4000, 1) NOT NULL,
     Description varchar(50)
)

Also, please maintain these open questions:

How to restore from a differential backup? Date: 03/28/2002 06:46PM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=win2k&qid=20282791
How do I share a drive on a 98 machine to a 2000? Date: 02/12/2002 08:36PM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=win2k&qid=20266227

Thanks,
Anthony
0
 
Anthony PerkinsCommented:
Also, these two questions:
Need to select a field that is listed after another Date: 01/26/2002 07:55PM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20259868
I want to change the owner of all objects using a script. Date: 04/17/2002 08:23PM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20290760

Thanks,
Anthony
0
 
kkirt1Author Commented:
As far as I can tell you can only have one Identity field in a table.  This table is already established and has an identity field.  I am thinking the solution would be in the default value.

Any ideas?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
pkohlmilCommented:
You literally auto-increment a field that is not an identity. There is a similar question in this topic area that has some ideas that you can use. Here is one variation of those ideas that might be applicable to your case. Add this new field to your table and make it nullable. Add a trigger for INSERT. Determine the arithmetic between the Identity column and this new column. For example, if the first value in the identity column is 1 and you want this new field to start with 4000, then you will add 3999 to the identity column to get the value for the new column. In the trigger, find the Identity column's value (lots of ways to do this include IDENT_CURRENT) and add 3999 and use that value to update your field.
0
 
Anthony PerkinsCommented:
If you already have one, why do you want a second one?  The only difference I can see is that the first may be 1 based the second starts at 4000.  In other words SecondID = ID + 4000. Again, I can think of a couple of solutions, but I need more explanation.

Anthony
0
 
Anthony PerkinsCommented:
Yes, you can use a trigger to update this field with a new value offset by 4000.  But why, create a new physical field for something that should be calculated.  It is no different to storing the price, quantity AND total price (price * quantity).  This does not make any sense.  If you insisist in keeping an additional calculated column, than use a computed column.  There is in my view, no need to use a trigger or whatever to maintain a calculated physical column.

Anthony
0
 
crw030Commented:
kkirt1.  You didn't post whether you are using SQL 7.0 or 2k, but here at home I was able to create multiple IDENTITY columns on a single table each one offset by some random number.  You just can't set more than 1 primary key.

If calculating the column doesn't make sense then use acperkins idea.
0
 
mironCommented:
crw030,

you sould have encountered an error, error message reads:

Server: Msg 2744, Level 16, State 2, Line 1
Multiple identity columns specified for table 't'. Only one identity column per table is allowed.


if you did not encouter this error your installation of sql server is corrupted.

Cheers
0
 
mironCommented:
kkirt1,

I designed and developed a solution for an increment that is not constrained by those few limitations of sql server 'identity'. Let me know if you interested, blueopera@cmpmail.com

Cheers
0
 
CleanupPingCommented:
kkirt1:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now