Professional Web Applications Themes

heavy population of db - Microsoft SQL / MS SQL Server

Hi there. I need to populate a table with, hold on, 90.000.000 entries. They have to unique, starting with 10.000.000 and ending with 99.999.999. I reckon that i have to use a FOR-NEXT loop and maybe have to use some VBScript, but i rather that it was a SQL statement. Can anyone help me with the code? Regards tonny...

  1. #1

    Default heavy population of db

    Hi there.
    I need to populate a table with, hold on, 90.000.000 entries.
    They have to unique, starting with 10.000.000 and ending with 99.999.999.
    I reckon that i have to use a FOR-NEXT loop and maybe have to use some
    VBScript, but i rather that it was a SQL statement.
    Can anyone help me with the code?

    Regards tonny


    Tonny René Poulsen Guest

  2. #2

    Default Re: heavy population of db

    How about using an IDENTITY column with a starting seed of 10,000,000.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Tonny René Poulsen" <2003pc.dk> wrote in message
    news:%23EYbaSBQDHA.2244TK2MSFTNGP11.phx.gbl...
    > Hi there.
    > I need to populate a table with, hold on, 90.000.000 entries.
    > They have to unique, starting with 10.000.000 and ending with 99.999.999.
    > I reckon that i have to use a FOR-NEXT loop and maybe have to use some
    > VBScript, but i rather that it was a SQL statement.
    > Can anyone help me with the code?
    >
    > Regards tonny
    >
    >

    Andrew J. Kelly Guest

  3. #3

    Default Re: heavy population of db

    I tried to use the dbgenwin to generate the rows with data.
    After 1 hour i had generated 1.360.000 rows, so it would take some 67 hours
    to generate the lot.
    I figured that a loop of some kind would be quicker, but i'm not sure.

    but tks anyway
    Regards Tonny


    "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> skrev i en meddelelse
    news:evueubBQDHA.1748TK2MSFTNGP11.phx.gbl...
    > How about using an IDENTITY column with a starting seed of 10,000,000.
    >
    > --
    >
    > Andrew J. Kelly
    > SQL Server MVP
    >
    >
    > "Tonny René Poulsen" <2003pc.dk> wrote in message
    > news:%23EYbaSBQDHA.2244TK2MSFTNGP11.phx.gbl...
    > > Hi there.
    > > I need to populate a table with, hold on, 90.000.000 entries.
    > > They have to unique, starting with 10.000.000 and ending with
    99.999.999.
    > > I reckon that i have to use a FOR-NEXT loop and maybe have to use some
    > > VBScript, but i rather that it was a SQL statement.
    > > Can anyone help me with the code?
    > >
    > > Regards tonny
    > >
    > >
    >
    >

    Tonny René Poulsen Guest

  4. #4

    Default Re: heavy population of db

    Something like this ought to work much more quickly:

    create table From0to9999 (
    i int primary key
    )
    go

    insert into From0to9999
    select OrderID - 10248 + 830*(ProductID - 1)
    from Northwind..Orders, Northwind..Products
    where ProductID <= 13
    and OrderID - 10248 + 830*(ProductID - 1) < 10000
    select min(i), max(i), count(i) from From0to9999
    go

    select 10000000 + F1.i + 10000*F2.i
    into EightDigitNumbers
    from From0to9999 F1, From0to9999 F2
    where F2.i <= 8999


    Steve Kass
    Drew University

    Tonny René Poulsen wrote:
    >Hi there.
    >I need to populate a table with, hold on, 90.000.000 entries.
    >They have to unique, starting with 10.000.000 and ending with 99.999.999.
    >I reckon that i have to use a FOR-NEXT loop and maybe have to use some
    >VBScript, but i rather that it was a SQL statement.
    >Can anyone help me with the code?
    >
    >Regards tonny
    >
    >
    >
    >
    Steve Kass Guest

  5. #5

    Default Re: heavy population of db

    I misunderstood what you wanted at first. I thought you already had data
    but wanted to add an ID column. Is the ID the only thing in the table? If
    so what good is that? Anyway if you can have another column it gets easier,
    try this:

    CREATE TABLE #t (MyID TINYINT)
    CREATE TABLE YourTable (YourID INT IDENTITY(10000000,1) NOT NULL,MyID
    TINYINT NOT NULL)

    DECLARE x INT
    SET x = 0
    WHILE x < 10000
    BEGIN
    INSERT INTO #t VALUES (1)
    SET x = x + 1
    END


    SET x = 0
    WHILE x < 9000
    BEGIN
    INSERT INTO YourTable (MyID)
    SELECT MyID FROM #t
    SET x = x + 1
    END



    --

    Andrew J. Kelly
    SQL Server MVP


    "Tonny René Poulsen" <2003pc.dk> wrote in message
    news:eNnewkBQDHA.2852tk2msftngp13.phx.gbl...
    > I tried to use the dbgenwin to generate the rows with data.
    > After 1 hour i had generated 1.360.000 rows, so it would take some 67
    hours
    > to generate the lot.
    > I figured that a loop of some kind would be quicker, but i'm not sure.
    >
    > but tks anyway
    > Regards Tonny
    >
    >
    > "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> skrev i en meddelelse
    > news:evueubBQDHA.1748TK2MSFTNGP11.phx.gbl...
    > > How about using an IDENTITY column with a starting seed of 10,000,000.
    > >
    > > --
    > >
    > > Andrew J. Kelly
    > > SQL Server MVP
    > >
    > >
    > > "Tonny René Poulsen" <2003pc.dk> wrote in message
    > > news:%23EYbaSBQDHA.2244TK2MSFTNGP11.phx.gbl...
    > > > Hi there.
    > > > I need to populate a table with, hold on, 90.000.000 entries.
    > > > They have to unique, starting with 10.000.000 and ending with
    > 99.999.999.
    > > > I reckon that i have to use a FOR-NEXT loop and maybe have to use some
    > > > VBScript, but i rather that it was a SQL statement.
    > > > Can anyone help me with the code?
    > > >
    > > > Regards tonny
    > > >
    > > >
    > >
    > >
    >
    >

    Andrew J. Kelly Guest

  6. #6

    Default Re: heavy population of db

    Hi andrew.
    The purpose is to evaluate those millions of numbers against a statement
    that says:
    Let the first 4 digits be duplicated in the exact same way in the last 4
    digits i.e. 12341234
    Find a 2 digit number where the sum of those two equals 10 (i.e 28 = 10)
    and where the 2 digit number can be divided into all of the constructed
    8 digit numbers without any leftovers!! I'm not sure of the term.
    When the list is populated i would the find alle the desired numbers by
    using
    select left (number, 4), right (number,4)
    from numbers
    where left (nuber,4) = right (nubmer,4)
    The trick is to develope the easiest way to derive those 8 digits
    numbers
    I solved the puzzle thru excel in an other matter, so that is not my
    problem.
    I hope that this make sence, and your are right; a table with one column
    isn't good for anything.

    btw: Out of 1,360,000 rows there was only about 370 of those numbers!

    Thanks for your effort. This goes for every who chipped in

    Regards Tonny

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Tonny René Poulsen Guest

Similar Threads

  1. Population pyramid
    By kanpeter@graduate.hku.hk in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: July 25th, 10:02 AM
  2. dynamic list population
    By wwsteeb in forum Dreamweaver AppDev
    Replies: 2
    Last Post: February 25th, 09:41 PM
  3. Newbie needs help with list box population
    By Rigga in forum PHP Development
    Replies: 4
    Last Post: November 17th, 01:52 PM
  4. Replies: 46
    Last Post: October 21st, 08:59 AM
  5. Automatic population
    By John Vinson in forum Microsoft Access
    Replies: 0
    Last Post: July 11th, 05:21 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139