Migration from MSSQL to MySQL

1. Fetch table schema from MSSQL

Script

#!/usr/bin/env ruby
require 'rubygems'
require 'dbi'
db = DBI.connect('dbi:ODBC:dbintegration', 'epres_amir', 'epres_amir8899')
select = db.prepare("select * from information_schema.columns where table_name = 'ref_course' order by ordinal_position")
select.execute
while rec = select.fetch do
puts rec.to_s
end
db.disconnect

Output:

["DBIntegration", "dbo", "REF_COURSE", "COURSEID", "1", nil, "No ", "varchar", 15, 15, nil, nil, nil, nil, nil, nil, "iso_1", nil, nil, "SQL_Latin1_General_CP1_CI_AS", nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "COURSE_CODE", "2", nil, "YES", "varchar", 15, 15, nil, nil, nil, nil, nil, nil, "iso_1", nil, nil, "SQL_Latin1_General_CP1_CI_AS", nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "COURSE_DESCBM", "3", nil, "YES", "varchar", 200, 200, nil, nil, nil, nil, nil, nil, "iso_1", nil, nil, "SQL_Latin1_General_CP1_CI_AS", nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "COURSE_DESCBI", "4", nil, "YES", "varchar", 200, 200, nil, nil, nil, nil, nil, nil, "iso_1", nil, nil, "SQL_Latin1_General_CP1_CI_AS", nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "CREDIT_HR", "5", nil, "YES", "numeric", nil, nil, true, "10", 2, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "EXAM_COURSE", "6", nil, "YES", "varchar", 15, 15, nil, nil, nil, nil, nil, nil, "iso_1", nil, nil, "SQL_Latin1_General_CP1_CI_AS", nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "FACULTYID", "7", nil, "YES", "varchar", 15, 15, nil, nil, nil, nil, nil, nil, "iso_1", nil, nil, "SQL_Latin1_General_CP1_CI_AS", nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "ACTIVE", "8", nil, "YES", "varchar", 15, 15, nil, nil, nil, nil, nil, nil, "iso_1", nil, nil, "SQL_Latin1_General_CP1_CI_AS", nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "OWNERID", "9", nil, "YES", "varchar", 15, 15, nil, nil, nil, nil, nil, nil, "iso_1", nil, nil, "SQL_Latin1_General_CP1_CI_AS", nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "PROJECT_COURSE", "10", nil, "YES", "varchar", 15, 15, nil, nil, nil, nil, nil, nil, "iso_1", nil, nil, "SQL_Latin1_General_CP1_CI_AS", nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "FINAL_EXAM", "11", nil, "YES", "varchar", 15, 15, nil, nil, nil, nil, nil, nil, "iso_1", nil, nil, "SQL_Latin1_General_CP1_CI_AS", nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "EXAM_DURATION", "12", nil, "YES", "varchar", 15, 15, nil, nil, nil, nil, nil, nil, "iso_1", nil, nil, "SQL_Latin1_General_CP1_CI_AS", nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "COMMON_COURSE", "13", nil, "YES", "varchar", 15, 15, nil, nil, nil, nil, nil, nil, "iso_1", nil, nil, "SQL_Latin1_General_CP1_CI_AS", nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "COURSETYPE", "14", nil, "YES", "varchar", 15, 15, nil, nil, nil, nil, nil, nil, "iso_1", nil, nil, "SQL_Latin1_General_CP1_CI_AS", nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "COURSE_CATEGORY", "15", nil, "YES", "varchar", 1, 1, nil, nil, nil, nil, nil, nil, "iso_1", nil, nil, "SQL_Latin1_General_CP1_CI_AS", nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "COMMON_FAC", "16", nil, "YES", "varchar", 10, 10, nil, nil, nil, nil, nil, nil, "iso_1", nil, nil, "SQL_Latin1_General_CP1_CI_AS", nil, nil, nil]
["DBIntegration", "dbo", "REF_COURSE", "DATE_RELOAD", "17", nil, "YES", "datetime", nil, nil, true, nil, 3, "3", nil, nil, nil, nil, nil, nil, nil, nil, nil]

2. Create table in MySQL DB base on MSSQL schema

Script

require 'rubygems'
require 'active_record'

ActiveRecord::Base.establish_connection(
:adapter=> "mysql",
:host => "localhost",
:username => "sub_integration",
:password => "sub_integration",
:database=> "skt"
)

ActiveRecord::Schema.define do
create_table :subjects do |table|
   table.string :COURSEID
   table.string :COURSE_CODE
   table.string :COURSE_DESCBM
   table.string :COURSE_DESCBI
   table.integer:CREDIT_HR
   table.string :EXAM_COURSE
   table.string :FACULTYID
   table.string :ACTIVE
   table.string :OWNERID
   table.string :PROJECT_COURSE
   table.string :FINAL_EXAM
   table.string :EXAM_DURATION
   table.string :COMMON_COURSE
   table.string :COURSETYPE
   table.string :COURSE_CATEGORY
   table.string :COMMON_FAC
   table.string :DATE_RELOAD
   table.timestamps
end
end

3. Migration

Script:

#!/usr/bin/env ruby
require 'rubygems'
require 'dbi'
require 'active_record'


ActiveRecord::Base.establish_connection(
:adapter=> "mysql",
:host => "localhost",
:username => "sub_integration",
:password => "sub_integration",
:database=> "skt"
)

class Subject < db =" DBI.connect('dbi:ODBC:dbintegration'," select =" db.prepare('SELECT" rec =" select.fetch" subject =" Subject.new("> rec[0],
        "COURSE_CODE" => rec[1],
        "COURSE_DESCBM" => rec[2],
        "COURSE_DESCBI" => rec[3],
        "CREDIT_HR" => rec[4],
        "EXAM_COURSE" => rec[5],
        "FACULTYID" => rec[6],
        "ACTIVE" => rec[7],
        "OWNERID" => rec[8],
        "PROJECT_COURSE" => rec[10],
        "FINAL_EXAM" => rec[11],
        "EXAM_DURATION" => rec[12],
        "COMMON_COURSE" => rec[13],
        "COURSETYPE" => rec[14],
        "COURSE_CATEGORY" => rec[15],
        "COMMON_FAC" => rec[15],
        "DATE_RELOAD" => rec[16]
)
subject.save
end
db.disconnect