Solved

SQL Query auto increment

Posted on 2011-03-10
6
632 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
[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
  • 3
  • 3
6 Comments
 
LVL 143

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 143

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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

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

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Display field if column exists 7 58
Estimating my database size 7 51
Please explain Equi-join 3 44
T-SQL: Wrong Result 7 39
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

738 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