Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2004
    Posts
    62

    Post Unanswered: Populate data using sql

    Greetings
    I have a record with this format

    id,start,end
    1,100,10000

    can i populate records from 100 to 10000 based on that 1 record

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you can, what would you like to populate into those rows?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2004
    Posts
    62
    100,101,102 .... 10000
    can it be done using insert statement

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please run SHOW CREATE TABLE for the table that you want to insert these numbers into
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2004
    Posts
    62
    Thanks r937 for the reply
    I have 2 tables, 1 is the range table and the other one is the raw table. The raw table is generated by the series of range in range table.Current i'm using java loop the range table and insert into raw table.

    range table
    id int(10) autoincrement,
    start_callno int(10),
    end_callno int(10),

    sample data
    1,1001,2000
    2,2001,3000


    raw table
    id int(10) autoincrement,
    callno int(10),
    1,1001
    2,1002
    3,1003 ......

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you don't have one already, create yourself an integers table --
    Code:
    create table integers (i integer not null primary key);
    insert into integers (i) values
    (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
    then run this query --
    Code:
    select H.i*100 + T.i*10 + U.i +1 as N
      from integers as H
    cross 
      join integers as T
    cross 
      join integers as U
     where H.i*100 + T.i*10 + U.i +1 between 100 and 1000
    with me so far?

    now, change it slightly so that you let the range table drive the insert --
    Code:
    insert
      into raw
         ( callno )
    select H.i*100 + T.i*10 + U.i +1 as N
      from integers as H
    cross 
      join integers as T
    cross 
      join integers as U
    inner
      join range  
        on H.i*100 + T.i*10 + U.i +1
           between start_callno and end_callno
    remember, the cross join of the integers table produces numbers 0 through 999, so keep that in mind if you want to go above 999 (the way i did in the first query above)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2004
    Posts
    62
    Thanks r937 for the fast reply,
    I tried the code, but i still don't understand how it works

    select H.i*100 + T.i*10 + U.i +1 as N
    from integers as H
    cross
    join integers as T
    cross
    join integers as U
    inner
    join range
    on H.i*100 + T.i*10 + U.i +1
    between start_callno and end_callno

    I have my range table in test
    1,53828100060081,53828100060100

    That query return 0 results

    Thank you in advance

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you ran my first query you would see that it produces the numbers between 100 and 1000

    notice the +1 in the query?

    just change that to +53828100060081

    play with it a little, you will soon understand it
    Code:
    select H.i*100 + T.i*10 + U.i +53828100060081 as N
      from integers as H
    cross 
      join integers as T
    cross 
      join integers as U
     where H.i*100 + T.i*10 + U.i +53828100060081 
           between 53828100060081 and 53828100060100
     order by 1  
    
    N
    53828100060081
    53828100060082
    53828100060083
    53828100060084
    53828100060085
    53828100060086
    53828100060087
    53828100060088
    53828100060089
    53828100060090
    53828100060091
    53828100060092
    53828100060093
    53828100060094
    53828100060095
    53828100060096
    53828100060097
    53828100060098
    53828100060099
    53828100060100
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2004
    Posts
    62
    Thanks a lot, and it works with this method

    select H.i*100 + T.i*10 + U.i +53828100060081 as N
    from integers as H
    cross
    join integers as T
    cross
    join integers as U
    where H.i*100 + T.i*10 + U.i +53828100060081
    between 53828100060081 and 53828100060100
    order by 1
    Thank you

Posting Permissions

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