Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2002-05-29
10
Medium Priority
?
303 Views
Last Modified: 2008-02-01
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
Comment
Question by:kkirt1
10 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 150 total points
ID: 7043719
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7043721
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
 

Author Comment

by:kkirt1
ID: 7044291
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 2

Expert Comment

by:pkohlmil
ID: 7044805
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7044836
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7044851
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
 
LVL 1

Expert Comment

by:crw030
ID: 7050326
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
 
LVL 9

Expert Comment

by:miron
ID: 7242644
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
 
LVL 9

Expert Comment

by:miron
ID: 7242648
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
 

Expert Comment

by:CleanupPing
ID: 9280430
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question