Solved

SQL Query auto increment

Posted on 2011-03-10
6
597 Views
Last Modified: 2012-05-11
I need help with creating an auto increment number starting at a specifc range and length of digits.

Example:

000200    Joe
000201    Abbey
000202    Mike
000203    Jeff

I need the 6 digit number to start at 200 and increment throughout the list.
0
Comment
Question by:holemania
  • 3
  • 3
6 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35100809
well, autoincrement can start at any value.
however, the "length" is a formatting issue, and not a data issue, at least conceptually speaking.

you have 3 options:
* start from 1000200 instead of 200, and return only the last 6 digits
* start from 200, and left-pad with leading 000
* you don't use a auto-number field, but a text field, and build the "sequencing" yourself (somehow). not recommended

in all 3 options, you likely have to consider the case when you get over the 999999 value ... you shall come to 1000000, or back to 000000, which both will result in other problem.

take this tip: don't do it. just put a numerical autonumber, and that should be ok.

0
 

Author Comment

by:holemania
ID: 35108907
Hi angelIII,

The list will never have more than 10,000 records.  Over the course of 16 years of the database being utilized, we have about 3000 vendors in this list.  I need to auto number the vendor ID since how it was originally created, there's no restriction on Vendor ID.  So numeric, alpha, and alphnumeric is use.  I want to standardize the vendor ID to a 6 digit starting from 200 as my starting number.

After playing with it this is what I come up with.

RIGHT('000000' + CONVERT(VARCHAR, (ROW_NUMBER() OVER (ORDER BY NAME) +200)), 6) AS ID

It seems to work.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35108987
only that ROW_NUMBER() will depend on the name's list.
so, if you add a new vendor in the list somewhere, the row_number() would change.
but I assume that you just want to update the existing list with this?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:holemania
ID: 35109667
Yes that is correct.  I just need to update the existing list.  
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35109709
this article explains how to formulate UPDATE with JOINS:
http://www.experts-exchange.com/A_1517.html

which is what you need to do, because you cannot update directly with row_number() function ...
0
 

Author Closing Comment

by:holemania
ID: 35109727
Thanks AngelIII.  Since you were the only one that responded, I have given you points.  What you pointed out is very good tip.  

For what I need to do, I think the Row_Number will work for me since I just need to randomly reassign 6 digits to a list of vendors that I need to rename the Vendor IDs to.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now