Solved

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

Posted on 2002-05-29
10
275 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 50 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Viewers will learn how the fundamental information of how to create a table.

820 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