#!/usr/bin/perluse 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 "ok\n"; } $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_str\n"); #close(file); printf("组合字符串时间%.1f seconds.\n",time-$startTime);$sth_select->finish;$dbh_mssql->disconnect; # open(file,">>all_export_data_fre.txt"); # syswrite(file,"$data_str\n"); # 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_sql\n"); # 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_name\n"); syswrite(file,"$sql_select\n"); syswrite(file,"$insert_sql\n"); 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_str\n"); syswrite(file,$format_time."\n"); close(file); }else{ open(file,">>$okouttxtname"); syswrite(file,"$table_name\n"); syswrite(file,"$sql_select\n"); ($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_select\n"); close(file); $cols->finish; $dbh2->disconnect; ($cols_select,$cols_insert,$cols_count,\@cols_types); } 总结 以上是内存溢出为你收集整理的sqlserver数据导入mysql六:对上一轮导数据时出错的sql再执行一遍全部内容，希望文章能够帮你解决sqlserver数据导入mysql六:对上一轮导数据时出错的sql再执行一遍所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）