virgerb-weblog

Okay, yesterday I had the problem of a slow data transfer between MySql and MsSql express edition 2005. Around 120000 records in one table I took almost 30 minutes.

Since I’m inserting it row by row I thought -> that should go faster. So After some googling I found that with the new sql express edition 2008 (MSSQL) that you can do a sql syntax that is the same like MySql.

Something like: insert into table (column1, column2) values (‘aae’, ‘ert’), (‘dsf’, ‘erg’), (‘rer’, ‘tea’);

So I updated my visual studio 2008 and the sql express edition.

After that I wrote something like this to achieve what I needed. Keeping in mind that maximum of (…),(…)(…) is a 1000 records. Because I’m having around 120000 records I loop and keep the records around 1000.

Here is my code:

var count = “SELECT COUNT(*) as rowcount FROM mysqlTable GROUP BY id”; //query to fetch data from the MySql Database
var countReader = Retrieve(count); //goes to a function that gets an IDataReader back for the MySql database
var countRows = 0;
try
{
while(countReader.Read()) {
countRows += int.Parse( countReader.GetValue(0).ToString() );
}
} catch(Exception ex) {
Console.WriteLine(ex.StackTrace + ” /n” + ex.Message);
} finally {
if (countReader != null) { countReader.Close(); _crud.close_conn(); }
}

for(var a = 0; a < countRows;)
{
var sql = “SELECT id, column1, column2 FROM mysqlTable LIMIT ” + a + “, ” + (999) + “”;

var reader = Retrieve(sql); // retrieves the IDataReader for MySql
try
{
var builder = new StringBuilder();
builder.Append(“INSERT INTO mssqlTable(id, column1, column2) VALUES “);

while (reader.Read()) {
try
{
builder.Append(“(” + reader.GetValue(0) + “, ” + reader.GetValue(1) + “, ” +
reader.GetValue(2) + “), “);
} catch (Exception ex) {
Log.LogMessageToFile(“Import.cs -> InsertTrafficHod: ” + ex.StackTrace + ” /n” + ex.Message);
}
}
var sqlDB = builder.ToString(0, builder.Length – 2);

if (!InsertDB(sqlDB)) //insert into the MSSQL 2008 express edition and return a boolean
{
Console.WriteLine(“No insert happend!”);
}
} catch (Exception ex) {
Console.WriteLine(ex.StackTrace + ” /n” + ex.Message);
} finally {
if (reader != null) { reader.Close(); _crud.close_conn(); } //close all connections
}
a = a + 999; //set counter to retrieve next block of values
}

Maybe this isn’t the right solution. So if somebody has another better, faster solution to copy data from MySql To MsSql, just let me know. But for now this is good enough.

Greetzz and until next post.