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?
kkirt1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.