Populate data using sql

pakcik_kantin · #1 (**permalink**) 09-13-06, 05:15

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

r937 · #2 (**permalink**) 09-13-06, 06:49

yes, you can, what would you like to populate into those rows?

pakcik_kantin · #3 (**permalink**) 09-13-06, 20:43

100,101,102 .... 10000
can it be done using insert statement

r937 · #4 (**permalink**) 09-13-06, 20:52

could you please run SHOW CREATE TABLE for the table that you want to insert these numbers into

pakcik_kantin · #5 (**permalink**) 09-13-06, 22:51

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

r937 · #6 (**permalink**) 09-14-06, 05:28

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)

pakcik_kantin · #7 (**permalink**) 09-14-06, 23:54

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

r937 · #8 (**permalink**) 09-15-06, 03:43

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

pakcik_kantin · #9 (**permalink**) 09-15-06, 04:35

Thanks a lot, and it works with this method

Quote:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech