Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2002-05-29
10
Medium Priority
?
297 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: 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!

 
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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

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…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

722 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