Thread: easy way to insert a range of values?

1. 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?

--Mark

2. 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```

3. 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. 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

5. 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. 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 .....

7. You da man!

Thanks for giving me things to think about.

Posting Permissions

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