Create table of unique alpha numbers to use as sku's
-
Hello all,
I am new in the since of posting in the forum I do have some past experience with SQL mostly in v2000/v2005.
What I have, is a need for a table of unique alpha numeric values that I can use for sku numbers and printing simple bar code labels for our products. (I do not need barcoding help as my label software just needs the number).
I will be populating in blocks of 15,000 skus to start and will need to remove any duplicates if they so happen to exist after the initial population.
The alpha part will come from a separate table of values.
This table is as so:
Table = SkuCategories
Table Columns =
(t_id IDENTITY , skuCategory_Name nvarchar(MAX), skuCategory_KeyCode char(10))
Values = (t_id, 'SomeCategory Product', 'SP')
What I need is the ability to run a statement, cursor or whatever method to populate another table with a set number of records with a unique sku number using the 'skuCategory_KeyCode' column value 'SP'
Where "S" is inserted at the beginning and "P" added at the end of a unique 9 digit number.
For instance or a sample of the value I am looking to get is as follows.
sku= "S123412345P"
The sku list table needs the following columns
(id IDENTITY, sku VARCHAR, datecreated (TimeStamp), isUsed BIT default 0)
Thank you and go easy on me as this is my first post!
-
Hi and welcome to the forums. Don't worry I don't bite and usually gentle.
Let me see if I understand what you are looking for. It sounds like what you need is a way to generate 9 digit numbers and stuff an "s" on the front and a "p" on the end? Do these values have to be random or is sequential acceptable?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Hi,
The s and the p are pulled from the table of categories, but yes in sense you are correct.
Sequential would be okay for the 9 digits, how ever in thought how would the numbering begin?
Our plan is to place a sku label on products and no rhyme nor reason to what the number part is, just that when someone looks at the sku or enters it in the catalog they know by the two letters what category it belongs to. And yes there really is no reason why the front and rear on the letters, just thought it was a little more creative.
thanks
-
How are your products uniquely identified, Greg?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Hi Chris,
To answer your question there not, as I stated the 9 digit number has no identifiable part as they can be unique random numbers and in any case if there were to be some non unique numbers they can be removed after the initial insert of 15,000 records. I do not believe that per category we will ever have more than the 15,000 initial sku numbers. Each category will have a table of 15,000 sku numbers and as far as the 9 digit number part of the sku being unique across tables, that does not matter as the two letters will make them unique in the overall catalog, and no category will exist with the same two letters. This is not a what if this or that scenario.
Thanks for your reply
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply