insert into bar select * from foo
[download]

Yep, but the tables are on different databases hence DBI, with two diffent dbhandles. Sorry, I should have mentioned that .. :)

You can build the insert string with the NAME attribute of the statement handle.
From the docs:

`NAME'  (array-ref, read-only)
Returns a reference to an array of field names for each
column. The names may contain spaces but should not be
truncated or have any trailing space. Note that the
names have the letter case (upper, lower or mixed) as
returned by the driver being used. Portable applications
should use the NAME_lc entry elsewhere in this document
or the NAME_uc entry elsewhere in this document.

print "First column name: $sth->{NAME}->[0]\n";
[download]

This is more of a SQL question than a DBI question. For inserting rows you can skip the column names if you specifiy values in order and for every column.

Here is the format:

 insert into table values (val1, val2, val3, ... valN);
[download]

where table has N columns

you must have a value for each column, even if that value is NULL(undef).

If you can't use native utilities for import/export (bcp?)
I would suggest to use bind columns for speed reasons...
(see this excellent node...)

"Only Bad Coders Badly Code In Perl" (OBC2IP)

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

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