in reply to DBI question

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

Replies are listed 'Best First'.
Re: Re: DBI question
by arhuman (Vicar) on May 09, 2001 at 18:57 UTC
    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)
[reply]
Re: Re: DBI question
by Anonymous Monk on May 09, 2001 at 19:45 UTC
    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.... ???
[reply]
      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]

In Section Seekers of Perl Wisdom