Transfer data from SQL Server to MySQL

use mssqltips;
go

drop table if exists dbo.Test;
create table dbo.Test(id int, c varchar(30), d date);
go

insert into dbo.Test(id, c, d)
values(1, 'hello', '2020-01-01'),(2, 'world', '2020-01-02')
,(3, 'good', '2020-01-03'),(4, 'morning', '2020-01-04');

use mssqltips;
create table `Test` (id int, c varchar(30), d date);

# Load Data from SQL Server to MySQL via MySQLBulkLoader class
add-type -Path "D:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.21\Assemblies\v4.5.2\MySql.Data.dll";
#uncomment the following line to calculate the time used to do the loading
#$start = get-date; 
 
$sql_instance = 'InsightR17DBDEV'; #assume we use windows authentication
$sql_db = 'MSSqlTips'; # note: your db system may be case sensitive
$csv_file = "c:\temp\mssql_test.csv"
$mysql_instance = '<mysql_server_name>';
$mysql_user = '<mysql_user, such as root>';
$mysql_pwd = '<password to connect to mysql>';
bcp dbo.test out $csv_file -S $sql_instance -d dba -t"," -T -c; #export sql table to a csv file
 
$conn_str="server=$mysql_instance;user=$mysql_user;password=$mysql_pwd;database=$sql_db;port=3306;AllowLoadLocalInfile=true";
$mysql_conn = new-object MySql.Data.MySqlClient.mysqlconnection($conn_str);
$mysql_conn.Open();
$bulk = New-Object Mysql.data.MySqlClient.MySqlBulkLoader($mysql_conn);
 
$bulk.Local=$true; # this is important setting
$bulk.FileName = $csv_file;
$bulk.TableName = 'Test'; #destination table on MySQL side
$bulk.FieldTerminator=',';
$bulk.NumberOfLinesToSkip=0;
$bulk.LineTerminator="`n";
#$bulk.FieldQuotationCharacter = '"';
#$bulk.FieldQuotationOptional = $false;
$bulk.Load() | out-null # without out-null, it will show # of rows inserted
<# uncomment to calculate the time taken
#calculate the time used 
write-host "Total time for method with [mysqlbulkloader] is" -ForegroundColor green;
(get-date)-$start
#>
$mysql_conn.Close(); 

# Load Data from SQL Server to MySQL via MySQLCommand class
add-type -Path "D:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.21\Assemblies\v4.5.2\MySql.Data.dll";
#uncomment the following line to calculate the time used to do the loading
#$start = get-date; 
 
$sql_instance = 'InsightR17DBDEV'; #assume we use windows authentication
$sql_db = 'MSSqlTips'; # note: your db system may be case sensitive
$csv_file = "c:\temp\mssql_test.csv";
$csv_file2 = "c:\\temp\\mssql_test.csv"
$mysql_instance = '<mysql_server_name>';
$mysql_user = '<mysql_user, such as root>';
$mysql_pwd = '<password to connect to mysql>';
bcp dbo.test out $csv_file -S $sql_instance -d dba -t"," -T -c; #export sql table to a csv file
 
$conn_str="server=$mysql_instance;user=$mysql_user;password=$mysql_pwd;database=$sql_db;port=3306;AllowLoadLocalInfile=true";
$mysql_conn = new-object MySql.Data.MySqlClient.mysqlconnection($conn_str);
$cmd = New-Object Mysql.data.MySqlClient.Mysqlcommand;
$cmd.CommandText = "load data local infile '$csv_file2' into table Test fields terminated by ',' enclosed by '' escaped by '\\' lines terminated by '\r\n';"
 
$cmd.Connection = $mysql_conn;
$mysql_conn.Open();
$cmd.ExecuteNonQuery();
<# uncomment to calculate the time taken
#calculate the time used 
write-host "Total time for method with [mysqlcommand] is" -ForegroundColor green;
(get-date)-$start
#>
$mysql_conn.Close(); 

use  mssqltips;
go
; with t1 as (
select 1 as c union all select 1 as c)
, t2 as (
select t1.c from t1 inner join t1 t on t1.c = t.c)
, t3 as (
select t2.c from t2 inner join t2 t on t2.c = t.c)
, t4 as (
select t3.c from t3 inner join t3 t on t3.c = t.c)
, t5 as (
select t4.c from t4 inner join t4 t on t4.c = t.c)
, t6 as (
select t5.c from t5 inner join t3 t on t5.c = t.c)
, t7 as (select rnk = row_number() over (order by c) from t6) 
 
insert into dbo.test (id, c, d)
select rnk , 'abc'+cast(rand(rnk)*100000 as varchar(12)), dateadd(day,  ceiling(rand(rnk)*1000), getdate())
from t7;
-- 1,048,576 inserted + the previous 4 records=total 1,048,580 records in sql server table