Posts Tagged ‘mysq’

copy DB from MS SQL to MYSQL

07/21/2011

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.

Advertisements