Auto generate BCP In/Out file(Perl)

根据数据库表的信息，自动生成bcp in/out脚本。使用Perl 5.6 语言，生成2个bat文件。

Configure file:  table.list

 

 

----BCP IN BEGIN------------------------------------------

$/="";  
open (FILES, "table.list"); 

$reads= ;
close(FILES);
$/="\n";
@_=split ( /\n/,$reads); 

foreach (@_){ 
  push (@tablelist,$_);
}  

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
print "@";
print "echo off\n";
print "rem Loading Scripts Generated Amber ",$mon+1,"/$mday/",$year+1900," $hour:$min:$sec\n";
print "rem Arguments Required\n";
print "rem  %1 - UserID\n";
print "rem %2 - Password\n";
print "rem %3 - Server\n";
print "rem %4 - Database\n";
print "\n";

print "rem Usage: bcp_in.bat UserName Password ServerName Database\n";
print "\n";
print "rem Check if all parameters passed.  If not display message and exit\n";
print "\n";
print ":begin\n";
print "if '%1' == '' goto usage\n";
print "if '%2' == '' goto usage\n";
print "if '%3' == '' goto usage\n";
print "if '%4' == '' goto usage\n";
print "if not  '%5' == '' goto usage\n";
print "\n";

print "echo **************** Truncate Architecture tables **************************\n";
print "echo.\n";
print "echo.\n";
print "echo Use %4 > sql.tmp\n";
print "echo go >>sql.tmp\n";

foreach $table(@tablelist)
{
  print "echo truncate table $table >>sql.tmp\n"; 
}
print "\n";
print "echo go >>sql.tmp\n"; 
print "rem --- Turncate commnad ( next line ) s commented. If needed truncate manually\n"; 
print "isql -U%1 -P%2 -S%3 -isql.tmp -osql.out\n"; 
print "\n";
print "\n";

foreach $table(@tablelist)
{
  print "echo **************** BCP IN $table **************************\n"; 
  print "bcp %4..$table in $table.dat -S%3 -U%1 -P%2 -c -t~ -JUTF8\n";
  print "\n";
}

print "\n";

print "echo.\n";
print "\n";
print "\n";
print "goto complete\n";
print "\n";

print ":usage\n";
print "echo **\n";
print "echo **  IMPROPER USAGE\n";
print "echo ** \n";
print "echo ** bcp_in.bat UserName Password ServerName Database\n";
print "echo ** \n";
print "echo ** Example: bcp_in.bat jsmith pswrd AIUSPL02 FASCHPRG4\n";
print "echo ** \n";
print "pause\n";
print "goto end\n";
print "\n";
print "\n";

print ":complete\n";
print "echo.\n";
print "echo **\n";
print "echo **** END: BCP IN of Architecture tables complete\n";
print "echo **\n";
print "\n";
print ":end\n";
print "@";
print "echo on\n";

 

----BCP IN END-------------------------------------------

 

 

----BCP OUT BEGIN------------------------------------------

 

$/="";  
open (FILES, "table.list"); 

$reads= ;
close(FILES);
$/="\n";
@_=split ( /\n/,$reads); 

foreach (@_){ 
  push (@tablelist,$_);
}  

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
print "@";
print "echo off\n";
print "rem Loading Scripts Generated (Amber) ",$mon+1,"/$mday/",$year+1900," $hour:$min:$sec\n";
print "\n";

print "rem Arguments Required\n";
print "rem  %1 - UserID\n";
print "rem %2 - Password\n";
print "rem %3 - Server\n";
print "rem %4 - Database\n";
print "\n";

print "rem Usage: bcp_out.bat UserName Password ServerName Database\n";
print "\n";

print "rem Check if all parameters passed.  If not display message and exit\n";
print ":begin\n";
print "if '%1' == '' goto usage\n";
print "if '%2' == '' goto usage\n";
print "if '%3' == '' goto usage\n";
print "if '%4' == '' goto usage\n";
print "if not  '%5' == '' goto usage\n";
print "\n";
print "\n";

foreach $table(@tablelist)
{ 
 print "echo **************** BCP OUT $table******************\n";
  print "bcp %4..$table out $table.dat -S%3 -U%1 -P%2 -c -t~ -JUTF8\n"; 
  print "\n";
}

print "\n";

print "echo.\n";
print "goto complete\n";
print "\n";

print ":usage\n";
print "echo **\n";
print "echo **  IMPROPER USAGE\n";
print "echo ** \n";
print "echo ** bcp_out_EzTravel.bat UserName Password ServerName Database\n";
print "echo ** \n";
print "echo ** Example: bcp_out_EzTravel.bat jsmith pswrd AIUSPL02 FASCHPRG4\n";
print "echo ** \n";
print "pause\n";
print "goto end\n";
print "\n";

print ":complete\n";
print "echo.\n";
print "echo **\n";
print "echo **** END: BCP OUT of Architecture tables complete\n";
print "echo **\n";
print "\n";

print ":end\n";
print "@";
print "echo on";

 

----BCP OUT END-------------------------------------------