sqlserver数据导入mysql六:对上一轮导数据时出错的sql再执行一遍

#!/usr/bin/perl

use Encode;
use Encode::CN;
use DBI;
use Switch;
use strict;
use Net::HandlerSocket;
use threads;
use Time::HiRes 'time';

	
	


my $aim_ip="192.168.0.208";
my $aim_db_name = "mysqldb";
my $hs_port = 9999;

my $source_name = "sqldb";
my $source_user_name = "sa";
my $source_user_psd = "123";

	
	my $db_name="mysqldb";
	my $location="192.168.0.208";
	my $port="3306";
	my $db_user="zoe";
	my $db_pass="123";

	
	my @a;
	print "请输入数字确认修复第几份表的入库";
 my $var=0;
$var=<STDIN>;
 chop ($var); 
 my $readtxtname="alltablename_repair_"."$var".".txt";
my $okouttxtname="alltablename_repairok_"."$var".".txt";
my $errorouttxtname="alltablename_repairerror_"."$var".".txt";
my $logouttxtname="alltablename_repairlog_"."$var".".txt";
my $insertouttxtname="alltablename_inserterror_"."$var".".txt";
my $string="";
open(FILE,"$readtxtname");    
 while (<FILE>) {  
  chomp;  
   if(defined($_ ))
  {
 push @a,$_;
   }
 
}
close(FILE);

 my $i=0;
while($i<=@a){
$string=$string.$a[$i];
$i++;
}
#print "$string";

my @b=split(/::/,$string);
my $count=@b;
my $index=0;
my @data;
my $sql_select;

my($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst);
my $format_time;


while($index+1<=$count)
{

$sql_select=@b[$index+1];
my $dbh=DBI->connect("dbi:ODBC:$source_name",$source_user_name,$source_user_psd,{RaiseError =>1});
my $sth=$dbh->prepare("select name,object_id from sys.all_objects where name='@b[$index]'");
  $sth->execute();
 while (@data=$sth->fetchrow_array())
 {
 #print $data[0].$data[1];
  my ($select_columns,$insert_columns,$column_count,$sort_column,$column_types);
  ($select_columns,$column_types)=get_columns($data[0],$data[1]);
   my $relt = export_data_in ($select_columns,$data[0],$column_types);	
print "okn";
   
 }

	 $sth->finish;
	 $dbh->disconnect;
	 $index++;
}
	 print '所有表的修复完毕！！！！'."n";

	 sub export_data_in
	 {
	
	  #构建SQL
          my($select_columns,$columns_count,$table_name,$column_types) = @_;

       
                        my $res0=export_data($table_name,$sql_select,$column_types);


	
 }
 
 sub export_data
 {
 my ($table_name,$column_types);
     my $data_str="";
	    my $insert_sql;
   eval{
         my $startTime=time;
        ($table_name,$column_types)=@_;
         my $dbh_mssql=DBI->connect("dbi:ODBC:$source_name",{RaiseError =>1});
		  	   $dbh_mssql->{LongTruncOk}=1;      
         $dbh_mssql->{LongReadLen}=1048576;
 
         my $sth_select=$dbh_mssql->prepare($sql_select);
		
         $sth_select->execute() or die "Cannot execute: ". $sth_select->errstr();
      
         ##生成标识ID
         my $gid=rand(3200);
     

	
         my @select_col;
        
	   my $select_data;
         #while($select_data=$sth_select->fetchrow_arrayref())		
        #{
		
		
            #    if($data_str ne "")
            #  {
                   #    $data_str="$data_str,";
              #  }
 
              #   $data_str=$data_str."[$gid,'+',['".join("','",@{$select_data})."']]";
				 
     
       # }
		  my $nn=0;
		  while($select_data=$sth_select->fetchrow_arrayref())
		   {
	              $select_col[$nn]=[@$select_data];  
				   $nn++;
           }
		      printf("读出时间%.1f seconds.n",time-$startTime);
			  
				  	   my $startTime=time;
			  my @col_data;
			  foreach my  $aref (@select_col) 
			  {
			 @col_data=@{$aref};
			 #print @col_data;
			 
		  if($data_str ne '')
        {
            $data_str.=',';
        }
        my $data_col="";
     
			 	
				 
			   for(my $nnn=0;$nnn<@col_data;$nnn++)
		  {
		   
				
             if ($data_col ne "")
            {
                $data_col.=",";
            }
            #对geometry值的特殊处理，如果有其它类型需要特殊处理，请在else后加eleif....
            if(@$column_types[$nnn] eq "geometry")
            {
                $data_col.=" GeomFromText('@col_data[$nnn]',4326)";
            }
			elsif(@$column_types[$nnn] eq "int")
			{   
			   if(@col_data[$nnn]>4200000000)
			   {
			   my $value=4294967295-@col_data[$nnn]+1;
			    $data_col.='-'."$value";
			   }
			   else
			   {
			   $data_col.="'@col_data[$nnn]'";
			   
			   }
			
			
			}
			elsif(@$column_types[$nnn] eq "date")
			{   
			   if(@col_data[$nnn] eq '1900-01-01')
			   {
			  $data_col.="'1000-01-01'";
			   }
			   else
			   {
			   $data_col.="'@col_data[$nnn]'";
			   
			   }
			
			
			}
				elsif(@$column_types[$nnn] eq "datetime")
			{   
			   if(@col_data[$nnn] eq '1900-01-01 00:00:00')
			   {
			  $data_col.="'1000-01-01 00:00:00'";
			   }
			   else
			   {
			   $data_col.="'@col_data[$nnn]'";
			   
			   }
			
			
			}
            else 
            {
                $data_col.="'@col_data[$nnn]'";
            }
               
      }
        #构建插入的时候的值字符串
        $data_str.="($data_col)n";	
			
			
			 } 
	
			  
			  

		   
		
		 
		 #open(FILE,">>all_export_data.txt");    
#syswrite(FILE,"$data_strn");    
#close(FILE); 
	
		
         printf("组合字符串时间%.1f seconds.n",time-$startTime);

$sth_select->finish;
$dbh_mssql->disconnect;

      
# open(FILE,">>all_export_data_fre.txt");    
# syswrite(FILE,"$data_strn");    
# close(FILE); 
 
   $data_str=encode("utf8",decode("gbk",$data_str));


 
          ##测试的时候，查看数据的语句。
     # print "n",$data_str,"n";
  $startTime=time;
         if($data_str ne "")
         {
           	my $data_base = "DBI:mysql:$aim_db_name:$aim_ip:$port";
        my $dbh_mysql=DBI -> connect($data_base,$db_user,$db_pass);
					$dbh_mysql->do("SET character_set_client = 'utf8'");
   $dbh_mysql->do("SET character_set_connection = 'utf8'");
    $data_str=~s///g;
        #插入的SQL语句
       $insert_sql = 'INSERT '.$table_name.'('.$insert_columns.') values '.$data_str .';';
	   
	   # open(FILE,">>all_export_data_insert.txt");    
# syswrite(FILE,"$insert_sqln");    
# close(FILE); 
	   
	   
        my $sth_mysql=$dbh_mysql->prepare($insert_sql);
        

        $sth_mysql->execute() or die $dbh_mysql->errstr()."n" ;
		 $sth_mysql->finish();
        $dbh_mysql->disconnect;
    }

    
  
      
        undef $data_str;
         printf("写入时间%.1f seconds.n",time-$startTime);
		 };
		
		  print "An error occurred: !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"  if  $@;
if($@)
{
 open(FILE,">>$logouttxtname");  
syswrite(FILE,"$table_namen");    
syswrite(FILE,"$sql_selectn");  
syswrite(FILE,"$insert_sqln"); 
syswrite(FILE,"$@n"); 
($sec,$isdst) = localtime(time()); 
 $format_time=sprintf("%d-%d-%d %d:%d:%d",$year+1990,$mon+1,$sec); 
syswrite(FILE,$format_time."n");    
close(FILE); 

 open(FILE,">>$errorouttxtname");  
syswrite(FILE,"$table_name::$sql_select::");    
close(FILE); 


 open(FILE,">>$insertouttxtname");  
syswrite(FILE,"$data_strn");  
syswrite(FILE,$format_time."n");    
close(FILE); 
}
else
{
	 open(FILE,">>$okouttxtname");  
syswrite(FILE,"$table_namen");  
syswrite(FILE,"$sql_selectn"); 
($sec,$format_time."n");  
close(FILE); 

}
undef $data_str;
print "An error occurred: $@"  if  $@;
}

 sub get_columns
 {
         print "loading columns of $_[0] n";
         my $sql="select col.name,tp.name,col.[is_identity] from sys.all_columns col
                                         inner join sys.types tp on col.system_type_id=tp.system_type_id  and col.user_type_id=tp.user_type_id
                                         where object_id=$_[1]";
         my $dbh2=DBI->connect("dbi:ODBC:$source_name",$source_user_psd);
         my $cols=$dbh2 -> prepare($sql);
         $cols->execute();
         my $cols_insert = "";
         my $cols_select = "";
        my $cols_count = 0;
         my $sort_column="";
         my @cols_types;
         my @col;
         while(@col= $cols->fetchrow_array())
        {
                 my ($col_name,$type_name,$is_identity)=@col;
                 @cols_types[$cols_count]=$type_name;
                 if($cols_count>0)
                 {
                         $cols_insert="$cols_insert,";
                         $cols_select="$cols_select,";
                 }
                 #else
                 #{
                         #$sort_column="[$col_name]";
                 #}
                 if($type_name eq "hierarchyid")
                 {
                         $cols_select = "$cols_select [$col_name].ToString() as [$col_name]";
                         $cols_insert = "$cols_insert$col_name";
                 }
				  elsif($type_name eq "nvarchar")
				 {
          			 $cols_select.="CAST((ISNULL(replace(replace(replace(replace([$col_name],'??',''),' ',char(10),char(13),'')) as TEXT) as [$col_name]";
                        $cols_insert = "$cols_insert$col_name";
				 
				 }    
				 		  elsif($type_name eq "varchar")
				 {
          					 # $cols_select.="CAST(ISNULL(replace(replace(replace(replace([$col_name],'?','?','') as TEXT) as [$col_name]";
								 $cols_select.="CAST((ISNULL(replace(replace(replace(replace([$col_name],'')) as TEXT) as [$col_name]";
                        $cols_insert = "$cols_insert$col_name";
				 
				 }
          elsif($type_name eq "nchar")
				 {
          					 $cols_select.="CAST((ISNULL(replace(replace(replace(replace([$col_name],'')) as TEXT) as [$col_name]";
                        $cols_insert = "$cols_insert$col_name";
				 
				 } 			 							 
                 elsif ($type_name eq "int")
                 {
                         $cols_select="$cols_select ISNULL([$col_name],0) as [$col_name]";
                        $cols_insert = "$cols_insert$col_name";
                } 
				elsif ($type_name eq "numeric")
                 {
                         $cols_select="$cols_select ISNULL([$col_name],0) as [$col_name]";
                        $cols_insert = "$cols_insert$col_name";
                }
			
				
				    elsif($type_name eq "geometry")
        {
            $cols_select = "$cols_select isnull([$col_name],'POINT (0 0)').STAsText() as $col_name";
            $cols_insert = "$cols_insert `$col_name`";
        }
				else 
                 {
                         $cols_select="$cols_select [$col_name]";
                        $cols_insert = "$cols_insert$col_name";
                }
				if($is_identity == 1)
			{
		
			$sort_column="[$col_name]";
			}
                 $cols_count++;
         }
		 
		  open(FILE,"$cols_selectn");    
close(FILE); 
		 $cols->finish;
         $dbh2->disconnect;
         ($cols_select,$cols_insert,$cols_count,@cols_types);
 }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!