Re: Data insert into DB

[jfroebe]

in reply to Data insert into DB

Well, this is really more of a SQL Server / T-SQL question than a Perl question. From the example data you provided and the amount of time it takes, it appears that you either use referential integrity (primary/foreign keys) and/or triggers for a set of tables...

1. Your file looks to be in a delimited format. Why not just use native bcp?
2. How long does it take for the inserts to run when using isql?
3. You're inserting one row at a time. One row per transaction. Is there a reason why you can't insert say 1,000 rows at a time before your execute()?
4. Instead of using single quotes for adding your data, use $dbh->quote($string) as there is no guarantee that your data file won't have embedded quotes that could mess up the insert. You may want to use $query = sprintf "INSERT INTO SOWND1 values(%s)", $dbh->quote($data[$j]);  $dbh->do($query);

Don't be too quick to blame Perl here.

[mje]

Not that I am disagreeing with what you've said jfroebe but the code provided dropped the table and recreated it without triggers or any keys.

[jfroebe]

oops! missed the drop. sorry about that