Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2015
    Posts
    27

    Unanswered: populating an empty table with sequential numbers

    I have a column within a table which is already truncated/deleted all records within (Microsoft SQL 2008). I have to now populate the column with sequential numbers up to 50,000 records arbitrary numbers (doesn't mater) up to 7 characters.

    Can any one help as to what SQL statement I need to write that will automatically polulate the newly empty table with A000001,A0000002,A0000003, or any form for that matter etc so that I can sort number the records within the table.

    I have approximately 50000 records which I need to sequentially entered and I really dont want to number the column manually via hand editing.

    Thanks in advance
    Last edited by Khan16; 01-15-15 at 14:34.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I would use an identity column or the ROW_NUMBER function.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Numbers table! http://gvee.co.uk/files/sql/dbo.numb...o.calendar.sql

    Code:
    SELECT (a.number * 256) + b.number As number
    FROM   (
            SELECT number
            FROM   master..spt_values
            WHERE  type = 'P'
            AND    number <= 255
           ) As a
     CROSS
      JOIN (
            SELECT number
            FROM   master..spt_values
            WHERE  type = 'P'
            AND    number <= 255
           ) As b
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2015
    Posts
    2

    Posible solution

    This sample shows how to insert 50000 numeric values sequencially.

    Code:
    create table t1 (c1 int)
    
    
    
    declare @counter int=0
    
    while (@counter<50000)
    begin
    
     insert into t1 values (@counter)
     set @counter=@counter+1
    end

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    I have a column within a table which is already truncated/deleted all records [sic] within (Microsoft SQL 2008). I have to now populate the column with sequential numbers up to 50,000 records [sic] arbitrary numbers (doesn't matter) up to 7 characters [sic]
    You used “column” correctly, but a record is nothing like a row in RDBMS. Well, what you described would use records because it is not a proper table! And you do not know that numeric values do not have character lengths. That was COBOL, where all data is in strings.

    In the early days, many file systems pre-allocated disk files. Each record (NOT row!) was described to the OS as so many bytes and given it “key” (not an RDBMS key) by the OS. Ever work with MPE on HP equipment? Probably not. Because of the pre-allocation, IMAGE/3000 databases could use hashing to locate a record (not a row!) and you had to allocate a prime number of records!

    This out-dated tech is what you want to mimic in the 21-st century. Stop it! Bad programmer! Bad!

    In RDBMS, a row models an entity that has just come into existence when you do an INSERT INTO. How can it have an identifier before it is exists? No, that is absurd! Even the OO people agree on OID needing an object to exist.

    Your mindset is fundamentally wrong.

    Can any one help as to what SQL statement I need to write that will automatically populate the newly empty table with 'A000001', 'A0000002', 'A0000003', or any form for that matter etc so that I can sort number the records within the table.
    Read this:
    https://www.simple-talk.com/sql/lear...quence-basics/

    I am not going to cut & paste it. You can use a kludge, but be better than that.

    I have approximately 50000 records [sic] which I need to sequentially [sic] entered and I really do not want to number the column manually via hand editing.
    Magnetic tapes are sequential records, but rows in a table are sets. Set are done in parallel! Done all at once as a set! Your entire approach to SQL is wrong; not a little wrong, but fundamentally wrong. In SQL you never enter records, one by one in sequence; it has to be a set at a time.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    George
    Home | Blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •