Depending on your volume of data, in the past I have used the bulk loader in MSSQL to insert many records at once. It does require the load file to be accessible by the server. One advantage is you can check your function is working as expected before changing the database.
#!perl
use strict;
use DBI;
my $dbh = dbh();
my $sql = 'SELECT COLUMN1,COLUMN2,COLUMN3
FROM TABLE1
WHERE COLUMN4 = ?';
my $sth = $dbh->prepare($sql);
$sth->execute('foo');
my $tmpfile = "c:\\temp\\public\\temp1.dat";
open TMP,'>',$tmpfile or die "$!";
while (my @f = $sth->fetchrow_array){
$f[2] = subrt($f[2]);
print TMP (join "\t",@f)."\n";
}
close TMP;
my $rv = $dbh->do('DELETE FROM TABLE2');
print "$rv records deleted from TABLE2\n";
$rv = $dbh->do( "
BULK INSERT TABLE2
FROM '$tmpfile'
WITH ( FIELDTERMINATOR = '\t' )" );
print "$rv records insert into TABLE2";
sub subrt {
reverse shift
}
# connect
sub dbh {
my $dsn = "DBI:ODBC:mssql";
my $dbh = DBI->connect($dsn, 'sa', '',
{RaiseError => 1, PrintError => 1})
or die (Error connecting " $DBI::errstr");
}
poj |