I’ve burned too much on this simple task, so I want to keep some gotchas it here.
The task: copy DB from MS SQL to MYSQL.
Tools
- MySQL migrate — I couldn’t make it work.
- http://www.convert-in.com/ — seem to be OK, but demo version is limited, so I’m not sure. Full version – $50.
- bcp – command line tool; it was extremely difficult (yet possible) to export to a file that MySQL can eat.
Code
dbcDataAdapter l_SourceDA = new OdbcDataAdapter(l_QuerySource, m_strSource);
OdbcDataAdapter l_TargetDA = new OdbcDataAdapter(l_QueryTarget, m_strTarget);
OdbcCommandBuilder cmb = new OdbcCommandBuilder(l_TargetDA); //mandatory! Even if I don't use cmb, this line is crucial as in creates INSERT command
string l_TableName = cmb.QuoteIdentifier(Xi_TableName, m_cTarget);
DataSet l_SourceDS = new DataSet();
DataSet l_TargetDS = new DataSet();
int l_Rows = l_SourceDA.Fill(l_SourceDS, Xi_TableName);
if (l_Rows <= 0)
{
Log("Empty table");
return;
}
l_TargetDA.Fill(l_TargetDS, l_TableName);
DataTable l_tblSource = l_SourceDS.Tables[Xi_TableName];
DataTable l_tblTarget = l_TargetDS.Tables[l_TableName];
int l_Errors = 0;
// Loop through the top five rows, and write the first column to the screen.
foreach (DataRow l_r in l_tblSource.Rows)
{
try
{
l_tblTarget.ImportRow(l_r);
l_tblTarget.Rows[l_tblTarget.Rows.Count - 1].SetAdded();
}
catch (OdbcException ex)
{
Log(ex.Message);
l_Errors++;
}
}
Log(string.Format("table {0}: {1} errors out of {2} records",
Xi_TableName, l_Errors, l_Rows));
DataSet l_Changed = l_TargetDS.GetChanges();
tblTarget.Update(l_Changed);
This is OK; but I needed support for tables with dash (“-”).
This was much harder; this thread was useful.
So two changes that fix it:
//create command
OdbcCommand cmdInsert = cmb.GetInsertCommand();
cmdInsert.CommandText = cmdInsert.CommandText.ToLower().Replace(Xi_TableName.ToLower(), "`" + Xi_TableName + "`");
//MSSQL use "[" and "]"; there are prefix/postfix fields somehere in the data adapter
...
//instead of just updating target data set:
OdbcDataAdapter l_NewTargetDA = new OdbcDataAdapter(l_TargetDA.SelectCommand);
l_NewTargetDA.TableMappings.Add("Table", l_TableName);
l_NewTargetDA.InsertCommand = cmdInsert;
l_NewTargetDA.Update(l_Changed);
Hopefully this will be useful for someone.
Advertisement
