Sorry about this. My groupwise client has a nasty habit of taking all my formatting out. If you like I can send an attachment to you. Just let me know.-Ian
>>>Ian Harisay <[EMAIL PROTECTED]> 09/07 8:33 am >>> A good approach is to to define your dataset. You are just using a CSV so I would do the following: 1) load CSV record into a data structure (hashes are great for this).2) insert record into table by way of a generic loader method. -- code snippet --use strict;use Text::CSV; #-- I use this because I am familiar with it.use DBI;my $dbh = DBI->connect($data_source, $username, $auth, \%attr);my $csv = Text::CSV->new(); #-- create CSV parsing object.#-- the file.open(FILE, datafile.txt) || die $!;while(<FILE>){ chomp; insertRecord(buildRecord($_)) || die some error occured; }sub buildRecord { my $line = shift; my $tbl_nam = 'table'; my @fields = qw(field1 field2 field3); my $record = {}; $csv->parse($line); my @data = $csv->fields(); for(my $i=0; $i<@fields; $i++ ){ $record->{$tbl_name}->{$_} = $data[$i]; }}sub insertRecord { my $record = shift; my $tbl_nam = keys %{$record}; #-- build sql my $sql = INSERT INTO $tbl_nam (; my $columns; my $placeholder; my @data; foreach my $col (keys %{$record->{$tbl_nam}} ){ $columns .= $col, ; $placeholder .= '?, '; push(@data, $record->{$tbl_nam}->{$col}); } $columns =~ s/, $//; $placeholder =~ s/, $//; my $dbh->do(INSERT INTO $tbl_nam ($columns) values($placeholders), undef, @data) || die $dbh->errstr();} -- end code snippet --this code has not been compiled, so you may find some syntax problems. I think the general logic is there though. Obviously some things can be done to optimize the code. I have a Db.pm I use to handle some of the optimization and don't want to go into detail here. Also, you can change your buildRecord method to be more generic by passing in the fields. Also, references could be used a little more extenisively to eliminate pass by value. Don't forget to commit your data if you don't have autocommit on. >>>NIPP, SCOTT V (SBCSI) <[EMAIL PROTECTED]> 09/07 7:04 am >>> I have a table with 22 columns, and my INSERT on this table is actually putting in data to possibly all 22 fields. My question is, is there a better way to write this INSERT? Here's what I have: while ($line = <DATA>) { $csv->parse($line); @data = $csv->fields; $sth = $dbh->prepare(qq{INSERT INTO AllMid_Data VALUES(?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?)}) or print Error with INSERT _prepare_: $DBI::errstr\n; $sth->execute(undef,$data[0],$data[1],$data[2],$data[3],$data[4],$data[5 ],$dat a[6],$data[7],$data[8],$data[9],$data[10],$data[11],$data[12],$data[13], $data[14 ],$data[15],$data[16],$data[17],$data[18],$data[19],$data[20]) or print Error with INSERT _execute_: $DBI::errstr\n; } The 'execute' statement is quite long, and almost all of the data comes from a single array. I don't think this is a big deal in and of itself, just looking for a better way to do this. Thanks. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com