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