Solved

SQL Query auto increment

Posted on 2011-03-10
6
638 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

626 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