Your are welcome! Richard
On Tue, Feb 7, 2012 at 5:46 PM, Omi Chiba <[email protected]> wrote: > Thank you, Richard ! > I came up the same idea using excutesql and it's done in a seconds !! > > db.executesql('INSERT INTO request (subject, result) SELECT subject, > result FROM result;') > * my database is db2 > > > In your case you need input of user so you need to import from CSV... > But I > > don't see why it's that long... > I think there is nothing we can do to improve the speed. As I > explained, it will generate the number of statement with the same > number of rows. > > My db2 is located in Japan and takes 1 min per 250 records. If I do > the same for the MS SQL Server located in my office it's about 5 > seconds per 250 records. For my app, it's always around 10 - 20 > records so I can live with this. > > > > > > On Feb 7, 4:32 pm, Richard Vézina <[email protected]> wrote: > > I was think of something like this... > > > > INSERT INTO dict_table (table_name) > > SELECT relname > > FROM pg_class > > WHERE relnamespace='2200' AND relname LIKE 'test_%' AND relname NOT LIKE > > '%_id_seq' > > > > That could be write in raw SQL or with web2py request syntax > > > > In your case you need input of user so you need to import from CSV... > But I > > don't see why it's that long... > > > > Maybe the way you process your CSV file is in cause... > > > > Richard > > > > > > > > > > > > > > > > On Tue, Feb 7, 2012 at 5:07 PM, Omi Chiba <[email protected]> wrote: > > > I think my case is the former. Like Sales support team prepare the > > > reply for the open request at the end of the day and upload. In the > > > evening, I have scheduled job on my AS400 do the upgrade from result > > > to request table with other information. > > > > > Insert each rows takes long time compared to update or delete. I think > > > it's slow because simply it will generate the number of statement with > > > the same number of rows. (500 rows = 500 insert statements) Can we > > > simply copy the selected rows to another ? I'm not sure. > > > > > On Feb 7, 3:19 pm, Richard Vézina <[email protected]> wrote: > > > > Do you have to let the user do that copy/paste because they add > result to > > > > kind of empty CSV/Excel spreadsheet to feed your system or you only > want > > > to > > > > move data around once? > > > > > > In later case you can make a request at DB level or in web2py > shell... > > > > > > Richard > > > > > > On Tue, Feb 7, 2012 at 3:48 PM, Omi Chiba <[email protected]> > wrote: > > > > > For example, I have two tables, "request" and "result". I want > user to > > > > > download all open request (status="1") from "request" table and > upload > > > > > with the result to "result" table. > > > > > > > What's the easy way to do ? > > > > > > > db.define_table('request', > > > > > Field('subject'), > > > > > Field('status', default="1"), > > > > > Field('result)) > > > > > > > db.define_table('result', > > > > > Field('subject'), > > > > > Field('result)) > > > > > > > Only thing I can think of is... > > > > > > > 1. delete result table > > > > > 2. Select request table with (status="1") and insert them to the > > > > > result table > > > > > > > But I'm on AS400 located in Japan and this additional insert > process > > > > > takes too much time for me. The best way is download the selected > data > > > > > with the header of result table... >
