Presentation, while the PowerShell script is what you see is what you run, and is goodįor debugging and troubleshooting, especially once deployed. The two methods are almost identicalīy using PowerShell, we can avoid the typical SSIS solution to do the same work.Īn SSIS package is what you see is what you design, it is good for explanation and We used two different methods one is the MySQLBulkLoaderĬlass and the other is the MySQLCommand class.
MYSQL INSERT SQL FILE HOW TO
In this tip, we discussed how to bulk load data from SQL Server to MySQLĬonnector/Net data provider. Using MySQLCommand class is about 184 milliseconds slower than using MySQLBulkLoaderĬlass, and such difference is negligible. Running this script, I get the following result: CommandText = "load data local infile ' $csv_file2 ' into table Test fields terminated by ',' enclosed by '' escaped by '\\' lines terminated by '\r\n' " $cmd.
MYSQL INSERT SQL FILE WINDOWS
$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 = '' # Load Data from SQL Server to MySQL via MySQLCommand class Load() | out-null # without out-null, it will show # of rows insertedĪfter running the script, I can see the following result: #$bulk.FieldQuotationCharacter = '"' #$bulk.FieldQuotationOptional = $false $bulk. TableName = 'Test' #destination table on MySQL side $bulk. Local = $true # this is important setting $bulk. $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 = '' īcp 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" #uncomment the following line to calculate the time used to do the loading # Load Data from SQL Server to MySQL via MySQLBulkLoader classĪdd-type -Path "D:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.21\Assemblies\v4.5.2\"
0 kommentar(er)
