SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Boston
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    easy way to insert a range of values?

    Here's what I want to do

    Code:
    insert into mytable set author='foobar', value=range(1,100);
    and have the result be 100 new rows in mytable where value=1, value=2, value=3, etc. and author='foobar' on all of them.

    Surely this must be possible, no?

    Thanks in advance,
    --Mark

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    first, you will need an integers table --
    Code:
    create table integers (i integer);
    insert into integers (i) values
    (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
    next, you insert 100 rows as follows:
    Code:
    insert into mytable (author, value)
    select 'foobar'
         , 10*t.i+u.i+1
      from integers as t
    cross
      join integers as u
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Boston
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow! That wasn't what I was expecting. While it is impressive there's just too much going on in that one line to employ it as a general quick and dirty solution. Although it is an interesting idea. Maybe I'll just create an integers table that counts from 1 to 10,000 (or whatever) and just hang on to that table. Then when I need to make a bunch of rows quick I could do something like

    Code:
    insert into mytable select 'foobar', i from integers where i between 1 and 100
    or

    Code:
    insert into mytable select 'foobar', i from integers where i between 1945 and 2005;
    for a quick range of birthyears.

    Not as efficient as the original solution because I have to keep my giant integers table around, but it is a one-line sql command and that beats a four-line PHP script for the quick-n-dirty solution I was looking for.

    Thanks for the idea!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by gdtrfb
    While it is impressive there's just too much going on in that one line to employ it as a general quick and dirty solution.
    i disagree

    here's your solution for integers between 1945 and 2005 --
    Code:
    select 1945+10*t.i+u.i as year
      from integers as t
    cross
      join integers as u
     where 10*t.i+u.i between 0 and 60
    hang in there, have confidence, you do not need any more than the integers 0 through 9 in that table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Boston
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree, having a table with 10 records is better than 10,000. And I can use this as an opportunity to learn more about cross joins.

    What if I wanted a series of rows populated with increasing string values a, b, c, ..., x, y, z, aa, ab, ac, ..., zx, zy, zz, aaa, aab...

    It's not that important, just wondering if there's an elegant solution for that too.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    increasing string values...

    hmm... let's see, there are ascii codes for each character...

    you could generate a through z (decimal codes 97 through 122) in the same way that we generated years from 1945 throught 2005

    but how to go from 122 to "double 97"? and how would you know to convert a number like 122 to one character but some other number (whatever it might be) to two characters?

    and why aa through az next? why not aa, ba, ca, ... cz?

    the elegance of the solution has nothing to do with the cross joins, but is more dependent on how you distinguish between one character, two characters, etc., and how you determine which ones you want

    it would be like asking to generate 1945 through 2005 and then 200601, 200602, etc. through 202012

    and in any case, increasing string values are hardly ever required

    instead, for strings, you usually just want a lot of random values

    for instance, names

    imagine a table of firstnames: 'Tom', 'Dick', 'Harry', etc.

    then imagine a table of lastnames: 'Smith', 'Jones', 'White', 'Clark', etc.

    now think cross join .....
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Boston
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You da man!

    Thanks for giving me things to think about.


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
  •