需求：
从sqlserver读取数据到本地数据库（sqlite3）中。
首先创建本地数据库的表：
[linuxtest test]$ sqlite3 test.db
SQLite version 3.3.9
Enter ".help" for instructions
sqlite> create table test20080702 (
...> cust_id number(10),
...> order_id number(10),
...> send_date date,
...> total varchar(10),
...> total_bargin_price varchar(10),
...> shipping_fee varchar(10),
...> product_name varchar(20),
...> bargin_price varchar(10),
...> order_quantity number(10),
...> allot_quantity number(10),
...> payment_provider_id number(10),
...> status number(2));
sqlite> .q
创建表完成
perl脚本
#!/usr/local/bin/perl
use strict;
use warnings;
use Data::Dumper;
use Getopt::Long;
use POSIX qw(strftime);
use DBI;
use Net::SMTP;
#$|=1;
print "S: ",strftime("%Y-%m-%d %H:%M:%S",localtime(time())),"n";
#my $type;
#GetOptions( "type=s" => $type);
#die("pls special a TYPEn") unless $type;
my $dbh = DBI->connect( "dbi:SQLite:/home/huming/test/test.db","","",{AutoCommit => 0} )
or die "$DBI::errstrn";
my $rdbh = DBI->connect( "dbi:Sybase:server=$server;database=$database",
"$user",
"$password",
{AutoCommit => 1} ) or die "$DBI::errstrn";
my $db_table = "dbo.order_items";
#get all_resouce
get_all_resouce();
$dbh->disconnect();
$rdbh->disconnect();
print "E: ",strftime("%Y-%m-%d %H:%M:%S",localtime(time())),"n";
exit;
#####
# get all_resouce
#####
sub get_all_resouce {
my ($cust_id,$order_id,$send_date,$total,$total_bargin_price,$shipping_fee,$product_name,$bargin_price,$order_quantity,
$allot_quantity,$payment_provider_id);
my $i = 0;
my $q = qq{select top 100
o.cust_id,
o.order_id,
convert(char(20),o.send_date,120) send_date,
o.total ,
o.total_bargin_price,
o.shipping_fee,
substring(oi.product_name,1,20) product_name,
oi.bargin_price,
oi.order_quantity,
oi.allot_quantity,
o.payment_provider_id
from dbo.orders(nolock) o ,dbo.order_items(nolock) oi
where o.order_status = '300'
and oi.order_id = o.order_id
and o.order_type = 0
};
my $sth = $rdbh->prepare($q) or die "$DBI::errstrn";
$sth->execute() or die "$DBI::errstrn";
$sth->bind_columns(undef, $cust_id,$order_id,$send_date,$total,$total_bargin_price,$shipping_fee,$product_name,$bargin_price,$order_quantity,
$allot_quantity,$payment_provider_id);
while ( $sth->fetch() ) {
$dbh->do(qq{insert into test20080702(cust_id,order_id,send_date,total,total_bargin_price,shipping_fee,product_name,bargin_price,order_quantity,
allot_quantity,payment_provider_id,status) values ('$cust_id','$order_id','$send_date','$total','$total_bargin_price','$shipping_fee','$product_name','$bargin_price','$order_quantity',
'$allot_quantity','$payment_provider_id','0')}) ;
$dbh->commit() if (++$i / 10000 ==0);
}
$dbh->commit();
}