Re: perl mysql - INSERT INTO, 157 columns

If you created (or altered) the table in such a way that the columns in the table are ordered the same as the columns in the input data file, then you can build your insert sql statement like this:

my $sth = $db->prepare( "describe my_big_table" );
$sth->execute;

my $rows = $sth->fetchall_arrayref;
$sth->finish;

my @column_names = map { $$_[0] } @$rows;

my $insert_sql =
    join( '',
      'insert into my_big_table (',
      join( ',', @column_names ),
      ') values (',
      join( ',', ( '?') x scalar @column_names ),
      ')'
    );

# check your work, if you like:
print join( "\n", "=== columns:", @column_names, '=== sql:', $insert_s
+ql, '' );
[download]

(updated to anonymize the table name)

Comment on Re: perl mysql - INSERT INTO, 157 columns Download Code

Replies are listed 'Best First'.
Re^2: perl mysql - INSERT INTO, 157 columns by Tux (Canon) on May 02, 2014 at 12:35 UTC
`describe` is not present in all databases :( More portable would be: `my $sth = $dbh->prepare ("select * from foo"); $sth->execute; my @fld = @{$sth->{NAME}}; $sth->finish; $sth = do { local $" = ","; $dbh->prepare ("insert into foo (@fld) values (@{[('?') x scalar @ +fld]})"); };` [download] Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]
Re^3: perl mysql - INSERT INTO, 157 columns by graff (Chancellor) on May 02, 2014 at 16:52 UTC
Given that the OP specifically mentioned mysql, I would not have posted the approach using "describe" if it didn't work in mysql. As for your alternative (for cases where "describe" doesn't work), I would hope that any type of database covered by DBD would at least support "select * from foo limit 1", and this would be a prudent usage for the task at hand, whether or not you actually need to execute the query in order to get field names. (BTW, thanks for the reminder about $")	[reply]
Re^4: perl mysql - INSERT INTO, 157 columns by choroba (Cardinal) on May 02, 2014 at 17:03 UTC
any type of database covered by DBD would at least support "select from foo limit 1"* Unfortunately, MS SQL Server throws an error: `Incorrect syntax near '1'` [download] What works, though, is `SELECT TOP 1 * FROM table` [download] لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ	[reply] [d/l] [select]
Re^4: perl mysql - INSERT INTO, 157 columns by gsiems (Deacon) on May 02, 2014 at 18:17 UTC
One would hope. However, last time I looked (v 11R2), Oracle still didn't support either limit or offset (you can fake it with rownum, but that's ugly).	[reply]
Re^3: perl mysql - INSERT INTO, 157 columns by erix (Prior) on May 02, 2014 at 12:53 UTC
my $sth = $dbh->prepare ("select from foo");* Surely that should be `my $sth = $dbh->prepare ("select * from foo where 1=0 ");` [download] Or something like that (limit, top, or whatever to not have the db think deeper than necessary...).	[reply] [d/l]
Re^4: perl mysql - INSERT INTO, 157 columns by Tux (Canon) on May 02, 2014 at 14:57 UTC
Usually not: the fetch starts only at the first fetch, so no table content is actually fetched at all. Some DBD's do not even need the execute. They have the table/field info readily available after the prepare. Enjoy, Have FUN! H.Merijn	[reply]
Re^5: perl mysql - INSERT INTO, 157 columns by erix (Prior) on May 02, 2014 at 19:25 UTC
Re^6: perl mysql - INSERT INTO, 157 columns by Tux (Canon) on May 03, 2014 at 12:52 UTC