asked on

SQL Query auto increment

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.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

holemania

ASKER

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.

Guy Hengel [angelIII / a3]

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?

holemania

ASKER

Yes that is correct. I just need to update the existing list.

Guy Hengel [angelIII / a3]

this article explains how to formulate UPDATE with JOINS:
https://www.experts-exchange.com/A_1517.html

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

holemania

ASKER

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.