Post reply

Creating sequential numbers

  • October 19, 2006 at 9:15 pm

    #115895

    I'm trying to figure a way to create (append to) unique numbers in a pre-existing table by adding rows containing similar (but non-unique) numbers.  I need to have the new numbers sequenced in the manner shown with the text field populated based upon input at the time the numbers are created.

    32764000021702Main Office
    32764000021703Main Office
    32764000021704Main Office
    32764000021705Main Office
    32764000021706Main Office
    32764000021707Main Office

    The bottom line is that I would want to be able to append new unique non-repeating numbers looking like this having provided the initial number by providing the quantity of new numbers I need to have created in sequence ascending, and the accompanying text added to the second row accompanying each of the numbers created.  

    I will be creating these new numbers (appending them to the existing list of numbers) as I need the numbers to catalog new inventory items.

    Thanks in advance for your thoughts...

  • October 20, 2006 at 10:06 am

    #667130

    I don't think I understand your question. Are u trying to create unique number(indentity) on an existing table(that has non unique keys)? Please explain us what u have and what u want to do.

    Thanks

    Sreejith

  • October 20, 2006 at 10:30 am

    #667138

    What is the data type of the number column? (please don't say float!)

    Your best bet is to make that column an identity column (rather than go through the hassle of managing an incrementing value yourself). The IDENTITY property can take two parameters, seed and increment.

    In your case the seed would be your max number + 1 and the increment would be one.

    Off the top of my head I don't know if you'll be able add the identity property simply with an ALTER TABLE statement. You may need to jump through some hoops (Rename the old table, create a new table to your specifications, set identity_insert on, move the data in to the new table).

    If the identity column is not for you, another option is to store a value in a seperate table. Whenever you are inserting, you could look this value up and supply it in your id column. The retrival code would look something like this:

    DECLARE @i BIGINT

BEGIN TRAN 
    SELECT @i = ID FROM IdTAble WITH(HOLDLOCK,TABLOCKX)
    UPDATE IdTable SET Id = Id + 1
COMMIT

    The HoldLock,TABLOCKX hints ensure no other processes can grab the same key value. The data type does not need to be BIGINT, CAST the value to whatever type you need.

    I hope this helps, but let me know if I can help further.

    -Robert

    SQL guy and Houston Magician

  • October 23, 2006 at 9:58 am

    #667404

    OK ... here's my re-explain.    Imagine you have an excel spreadsheet with the numbers and text shown in my initial post.  If you want the next 100 sequential numbers for the row, you would click on and select the last two or three existing numbers to show excel the pattern, then drag down and select the next 100 blank rows and release the mouse button ... and  excel would automaticially fill those rows with the next 100 sequential rows.   I want to do this exact same thing in TSQL in an pre-existing table with the number field set as Text as sometimes the numbers have leading zeros.

    Hope this clears up my explanation...

    Thanks!

     

  • October 23, 2006 at 10:14 am

    #667410

    Am I correct in thinking the other columns would be blank at that point?

    SQL guy and Houston Magician

  • October 23, 2006 at 10:19 am

    #667411

    Yes... correct.  In fact, there is only one other column in this table.. which would contain the repeating value of the location of the items in the inventory.. as shown in the initial post.  Example.. you might have 100 new numbers in sequence all going to the Louisana site.. so they would all say "Louisiana" in the second field.

  • October 23, 2006 at 11:03 am

    #667421

    How about something like this:

    CREATE TABLE #t1
(

    num VARCHAR(30),
    Location VARCHAR(30)
)
INSERT INTO #t1 (num, location)
VALUES('32764000021707', 'Boulder')
INSERT INTO #t1 (num, location)
VALUES('00999999999999999', 'Boulder')



----------------
DECLARE @newRows INT
SELECT @newRows = 25


INSERT INTO #T1 (num, Location)
SELECT RIGHT(REPLICATE('0',Length) + CAST(CAST(T1.num AS BIGINT) + Numbers.Number AS VARCHAR(30)),length), Location
FROM (SELECT TOP 1 num, Location, LEN(num) AS length 
        FROM #t1 
        ORDER BY CAST(num AS BIGINT) DESC
      ) t1
CROSS JOIN 
    (SELECT TOP 999 (i3.number*100 + i2.number*10 + i1.number) AS [number]
    FROM    (SELECT 0 AS [number] UNION ALL
                    SELECT 1 AS [number] UNION ALL
                    SELECT 2 AS [number] UNION ALL
                    SELECT 3 AS [number] UNION ALL
                    SELECT 4 AS [number] UNION ALL
                    SELECT 5 AS [number] UNION ALL
                    SELECT 6 AS [number] UNION ALL
                    SELECT 7 AS [number] UNION ALL
                    SELECT 8 AS [number] UNION ALL
                    SELECT 9 AS [number]) AS i1
                    CROSS JOIN
                    (SELECT 0 AS [number] UNION ALL
                    SELECT 1 AS [number] UNION ALL
                    SELECT 2 AS [number] UNION ALL
                    SELECT 3 AS [number] UNION ALL
                    SELECT 4 AS [number] UNION ALL
                    SELECT 5 AS [number] UNION ALL
                    SELECT 6 AS [number] UNION ALL
                    SELECT 7 AS [number] UNION ALL
                    SELECT 8 AS [number] UNION ALL
                    SELECT 9 AS [number]) AS i2 
                    CROSS JOIN
                    (SELECT 0 AS [number] UNION ALL
                    SELECT 1 AS [number] UNION ALL
                    SELECT 2 AS [number] UNION ALL
                    SELECT 3 AS [number] UNION ALL
                    SELECT 4 AS [number] UNION ALL
                    SELECT 5 AS [number] UNION ALL
                    SELECT 6 AS [number] UNION ALL
                    SELECT 7 AS [number] UNION ALL
                    SELECT 8 AS [number] UNION ALL
                    SELECT 9 AS [number]) AS i3 
             
    WHERE (i3.number*100 + i2.number*10 + i1.number) <> 0  
        AND  (i3.number*100 + i2.number*10 + i1.number) <= @newRows
    ORDER BY [number]
    ) Numbers

    SQL guy and Houston Magician

  • October 23, 2006 at 11:18 pm

    #667492

    Commando...

    First, I gotta share the normal diatribe about your numeric sequence and forgive me while I blast the designer... if you were the designer, sorry, but not sorry...  it looks like it contains an 5 digit branch code followed by a 9 digit sequence... WHY would anyone do such a nasty thing to a database?   Someone was apparently afraid that they would run out of numbers somewhere. 

    Ok... off the soap box...

    I think this is what you want... do keep in mind that if someone add's rows while this is adding rows, there could be a problem with some repeating numbers because of the use of MAX... that just can't be help unless we use a "Sequence Table" which would just be adding another level of complexity to this already nasty beast...

    --===== This just simulates your table and would NOT be included in your final code

        SET NOCOUNT ON

         IF OBJECT_ID('TempDB..#yourtable') IS NOT NULL

            DROP TABLE #yourtable

     CREATE TABLE #yourtable (Seq VARCHAR(14) PRIMARY KEY, Branch VARCHAR(20))

     INSERT INTO #yourtable (Seq,Branch)

     SELECT '32764000021702','Main Office' UNION ALL

     SELECT '32764000021703','Main Office' UNION ALL

     SELECT '32764000021704','Main Office' UNION ALL

     SELECT '32764000021705','Main Office' UNION ALL

     SELECT '32764000021706','Main Office' UNION ALL

     SELECT '32764000021707','Main Office'

     SELECT 'Table looks like this to start with...'

     SELECT * FROM #yourtable

    --===================================================================================

    --This is where the rubber meets the road to solve your problem.  Sub the correct table/column names in your real code EXCEPT FOR #MyTemp AND SYSCOLUMNS!!! DO NOT CHANGE THOSE!!!.

    --===== Declare some local variables

    DECLARE @BranchNum CHAR(5)

    DECLARE @SeqNum INT

    DECLARE @BranchName VARCHAR(20)

        SET @BranchName = 'Main Office'

    DECLARE @NumberOfRowsToADD INT

        SET @NumberOfRowsToADD = 100

    --===== This finds and splits the Max SEQ so we can work on it

     SELECT @BranchNum = LEFT(MAX(Seq),5),

            @SeqNum    = CAST(SUBSTRING(MAX(Seq),6,9) AS INT)

       FROM #yourtable

      WHERE Branch = @BranchName

    --===== This creates the new rows auto-magically in a temp table

        SET ROWCOUNT @NumberOfRowsToADD

     SELECT IDENTITY(INT,1,1) AS MyCount,

            CAST(NULL AS VARCHAR(14)) AS NewSeq,

            @BranchName AS Branch

       INTO #MyTemp

       FROM Master.dbo.SysColumns sc1,

            Master.dbo.SysColumns sc2

    --===== Create the new sequence numbers

     UPDATE #MyTemp

        SET NewSeq = @BranchNum+REPLACE(STR(@SeqNum+MyCount,9),' ','0')

    --===== Add the new records to your table

     INSERT INTO #yourtable

            (Seq,Branch)

     SELECT NewSeq AS Seq,

            Branch AS Branch

       FROM #MyTemp

       DROP TABLE #MyTemp

     SELECT 'Table looks like this after we added 100 rows...'

     SELECT * FROM #yourtable

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 24, 2006 at 8:10 am

    #667584

    I thought the same .. and no ... I'm not the designer.  

    Thanks.

  • October 24, 2006 at 8:13 am

    #667587

    Thanks.. I'll give it a go mate.

  • October 25, 2006 at 8:30 am

    #667878

    Jeff.. your code works great.. thanks.    One problem with it (the result of inadequate explanation on my part) is that when creating these new groups of numbers I periodiocially change branches, but when doing that I must maintain the sequence of un-duplicated numbers.   When I tried this with your code (made 100 numbers, then changed the line "set @branchname = 'Main Office'  to "set @branchname = "Other office'  ... got this error

     

    (100 row(s) affected)

    (100 row(s) affected)

    Server: Msg 515, Level 16, State 2, Line 29

    Cannot insert the value NULL into column 'Seq', table 'Test.dbo.#yourtable'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    (1 row(s) affected)

    (100 row(s) affected)

    Is this because your process sees the branch name as part of the unique sequence number when creating the tmp table?

     

  • October 25, 2006 at 3:18 pm

    #668019

    This creates 25 numbers, but doesn't find duplicates, and when run multiple times adds the same numbers... I don't see any way to get it to add the next group of numbers... am I missing something?   Below is what I got running it several times....

    Thanks!

    00999999999999999

    Boulder 00999999999999999

    Boulder 01000000000000000

    Boulder 01000000000000001

    Boulder 01000000000000002

    Boulder 01000000000000003

    Boulder 01000000000000004

    Boulder 01000000000000005

    Boulder 01000000000000006

    Boulder 01000000000000007

    Boulder 01000000000000008

    Boulder 01000000000000009

    Boulder 01000000000000010

    Boulder 01000000000000011

    Boulder 01000000000000012

    Boulder 01000000000000013

    Boulder 01000000000000014

    Boulder 01000000000000015

    Boulder 01000000000000016

    Boulder 01000000000000017

    Boulder 01000000000000018

    Boulder 01000000000000019

    Boulder 01000000000000020

    Boulder 01000000000000021

    Boulder 01000000000000022

    Boulder 01000000000000023

    Boulder 01000000000000024

    Boulder 01000000000000025

    Boulder 01000000000000026

    Boulder 01000000000000027

    Boulder 01000000000000028

    Boulder 01000000000000029

    Boulder 01000000000000030

    Boulder 01000000000000031

    Boulder 01000000000000032

    Boulder 01000000000000033

    Boulder 01000000000000034

    Boulder 01000000000000035

    Boulder 01000000000000036

    Boulder 01000000000000037

    Boulder 01000000000000038

    Boulder 01000000000000039

    Boulder 01000000000000040

    Boulder 01000000000000041

    Boulder 01000000000000042

    Boulder 01000000000000043

    Boulder 01000000000000044

    Boulder 01000000000000045

    Boulder 01000000000000046

    Boulder 01000000000000047

    Boulder 01000000000000048

    Boulder 01000000000000049

    Boulder 01000000000000050

    Boulder 01000000000000051

    Boulder 01000000000000052

    Boulder 01000000000000053

    Boulder 01000000000000054

    Boulder 01000000000000055

    Boulder 01000000000000056

    Boulder 01000000000000057

    Boulder 01000000000000058

    Boulder 01000000000000059

    Boulder 01000000000000060

    Boulder 01000000000000061

    Boulder 01000000000000062

    Boulder 01000000000000063

    Boulder 01000000000000064

    Boulder 01000000000000065

    Boulder 01000000000000066

    Boulder 01000000000000067

    Boulder 01000000000000068

    Boulder 01000000000000069

    Boulder 01000000000000070

    Boulder 01000000000000071

    Boulder 01000000000000072

    Boulder 01000000000000073

    Boulder 01000000000000074

    Boulder 32764000021707

    Boulder 32764000021707

    Boulder 

  • October 25, 2006 at 3:37 pm

    #668026

    The code I put up is split into two blocks. The first block create a temp table and inserts a few values, and the second block does a 'fill down' of the last value.

    If you run the second block (after ---------------) multiple times, you should get 25 new records. If you want more new rows change 25 to whatever you want, and you can also change the location to be configurable. Just replace location with a string literal or a variable.

    It is possible for duplicate records to be created. I suggest wrapping this in a transaction in a proc and using sp_getapplock to ensure you don’t get two simultaneous calls (see http://www.sqlservercentral.com/columnists/rcary/2649.asp) just make @newRows and @location parameters to the proc.

    I hope this helps.

    SQL guy and Houston Magician

  • October 25, 2006 at 5:06 pm

    #668044

    quote...but when doing that I must maintain the sequence of un-duplicated numbers

    Commando...

    I think the quote above explains it all and I may be the one that missed it... I thought that the "unique" portion of the sequence number was unique but only for each branch... for example, I thought that if you had two branches (BranchA = #10001, BranchB = #10002), that the following sequence numbers would be possible (note the duplication of the "unique" portion)...

    10001000000001
    10002000000001
    10001000000002
    10002000000002

    Again, I perceive the sequence number to be in two parts... the first part is 5 digits and could be interpreted as a "branch number" of sorts.  The 9 digits that follow that are actually what you are calling the UNIQUE number.  Are you saying that number should be unique even across branches?  If so, I have a fix so easy, you won't believe it but I need to know if this is how you want it before I crank any code for it...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 25, 2006 at 8:50 pm

    #668069

    Yes... the number must be unique even across branches....

    Thanks!

     

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply