Solved

SQL Query auto increment

Posted on 2011-03-10
6
616 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

810 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