Re: Re: DBI question

A bit of context here, I am writing an Ingres module that takes a list of many tables and builds copies of the tables on another database the number of rows being dependant on a passed "where clause".
So I thought that as DBI knows the column names on a "select *" it could automatically use them on an insert to an identical table( same no of columns, same names), without jiggery pokery.
as in INSERT INTO foo ( SELECT * FROM bar)
Not so.... ???

Comment on Re: Re: DBI question

Replies are listed 'Best First'.
Re: Re: Re: DBI question by Mungbeans (Pilgrim) on May 10, 2001 at 16:17 UTC
If the columns and names are the same then, as earlier suggest, `insert into foo select * from bar` will work fine. Depending on your RDBMS and how things are setup you may be able to specify a table in a different database as `database:bar` or some such, but this can run into problems with transactions etc. If you're doing bulk copies, it's probably faster to: dump table contents into text files turn off logging on target database load text files into target database tables (sqlloader for oracle, load for informix etc) Depending on volume of course...	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Re: Re: DBI question
by Mungbeans (Pilgrim) on May 10, 2001 at 16:17 UTC

insert into foo select * from bar

Depending on your RDBMS and how things are setup you may be able to specify a table in a different database as database:bar or some such, but this can run into problems with transactions etc.

If you're doing bulk copies, it's probably faster to:

dump table contents into text files
turn off logging on target database
load text files into target database tables (sqlloader for oracle, load for informix etc)

Depending on volume of course...

[reply]
[d/l]
[select]