Use Perl for database migration, from MSSQL to MySQL (2) Automatic table Creation

to put it bluntly, go directly to the Code .

#! /Usr/bin/perluse dBi; Use switch; # apply for connection resources, because Perl can only use ODBC to connect to SQL server. If the environment is not deployed, for more information, see the younger brother's blog article [Perl MSSQL] connector-> connect ('dbi: ODBC: # invisible ODBC database connection ##', '## concealed MSSQL username ##',' ## concealed MSSQL database password ##'); # obtain all tables to be imported to MySQL. all_objects where type = 'U' and is_ms_shipped = 0 and name <> 'sysdiagrams '"); $-> Execute (); While (@ DATA = $ something-> fetchrow_array () {# @ DATA = $ something-> fetchrow_array (); # disable loop during testing, read Only one table # get_columns ($ data [0], $ data [1]);} # $ DBH-> disconnect; # An error is returned after a disconnect statement is added, doesn't ODBC need to be disconnected? A little dizzy. # Retrieve all columns sub get_columns {$ dbh2 = DBI-> connect ('dbi: ODBC: # invisible ODBC database connection ##', '## concealed MSSQL username ##',' ## concealed MSSQL database password ##'); my $ SQL = "select Col. name, TP. name, Col. max_length, Col. [precision], Col. [scale], Col. [is_nullable], Col. [is_identity] From sys. all_columns colinner join sys. types TP on col. system_type_id = TP. system_type_id and Col. user_type_id = TP. user_type_idwhere object_id = $ _ [1] "; my $ Cols = $ dbh2-> prepare ($ SQL ); $ Cols-> execute (); my $ cols_str = ""; while (@ Col = $ Cols-> fetchrow_array () {($ col_name, $ type_name, $ max_length, $ precision, $ scale, $ is_nullable, $ is_identity) = @ Col; # type conversion... Switch ($ type_name) {Case "nchar" {$ type_name = "char";} case "bit" {$ type_name = "Boolean ";} case "ntext" {$ type_name = "text";} case "nvarchar" {$ type_name = "varchar" ;}else {$ type_name = $ type_name ;}} if ($ cols_str ne "") {$ cols_str = "$ cols_str, \ n" ;}# perform special processing on hierarchyid if ($ type_name EQ "hierarchyid ") {if ($ cols_str EQ "") {# $ cols_str = "$ cols_str 'id' int not null auto_increment primary key, 'parent _ id' int "; $ cols_str = "$ cols_str '$ col_name 'varchar (31)";} else {$ cols_str = "$ cols_str' $ col_name 'varchar (31 )";}} else {$ cols_str = "$ cols_str '$ col_name' $ type_name"; Switch ($ type_name) {Case "varchar" {# varchar (max) in SQL Server) if ($ max_length =-1) {$ cols_str = "$ cols_str (65535)" ;}else {$ cols_str = "$ cols_str ($ max_length )";}} case "numeric" {$ cols_str = "$ cols_str ($ precision, $ scale)" ;}# whether it can be empty if ($ is_nullable = 0) {$ cols_str = "$ cols_str not null" ;}# whether it is an Identifier if ($ is_identity = 1) {$ cols_str = "$ cols_str auto_increment primary key ";}}} # print "create table if not exists '$ _ [0]' ($ cols_str );"; exec_mysql ("create table if not exists '$ _ [0]' ($ cols_str);"); # $ dbh2-> disconnect; # An error will be reported when this sentence is added.} # create a sub exec_mysql {my $ db_name = "# hidden MySQL target database name #" in MySQL ##"; my $ location = "# hidden MySQL database IP address ##";

My $ Port = "# hidden MySQL database port #"; my $ data_base = "DBI: mysql: $ db_name: $ location: $ port "; my $ db_user = "# hidden MySQL database username #"; my $ db_pass = "# hidden MySQL Database Password ##"; my $ dbh3 = DBI-> connect ($ data_base, $ db_user, $ db_pass); my $ something = $ dbh3-> prepare ($ _ [0]); $ something-> execute () or die "error ::: _ [0] ::$ dbh3-> errstr"; $ dbh3-> disconnect ;}