Copying the data of one database table to another

Question

i'm trying to write a C# application that reads data from one table of OleDb database to another table of OleDb database. The source and destination table will be decided during run time by the user.

The way i'm doing it right now is, reading everything into OleDbDataReader, iterating through each row and inserting into other table.

  // create insert string
  insert = "INSERT INTO " + Dest_Table + " VALUES (";
  for(int i = 0; i < cols.Length; i++)       // cols is array of column names
  {
      string coma = ", ";
      if (i == cols.Length - 1)
      coma = " )";

      insert += "?" + coma;
  }

  // read each row and insert
  while (src_reader.Read())
  {
      dstcmd.CommandText = insert;

      for (int i = 0; i < cols.Length; i++)
      {
          string temp = "@" + cols[i];
          dstcmd.Parameters.AddWithValue(temp, src_reader[cols[i]]);

          // for debug purpose
          Console.Write(temp + "  " + src_reader[cols[i]] + "\n");
      }

      dstcmd.ExecuteNonQuery();

  }

I was wondering, is there a better and more efficient way to do data transfer between tables of different databases? Or is it the only way? I know that in SqlConnection, there's a SqlBulkCopy method that can do this, but what about OleDbConnection? Is there something similar to SqlBulkCopy?

Any help will be appreciated. Thanks.

That will be decided by the user. It could be MySql or Sql-Server or MS Access. Connection string for both databases will be provided during run time. — Billy, Mar 05 '18 at 15:58
This seems like a SDLC operation to me, rather than re-invent the wheel, could just use Red Gate products to achive this. https://www.red-gate.com/products/sql-development/sql-data-compare/ — Random_User, Mar 05 '18 at 16:00
Since i'm in University, i need to code the solution so can't really hand in the software that already exist on internet. I have implemented a solution. Just want to know if there is a better way. Since i'm new to SQL and ado.net world, every suggestion will be a lesson to learn. — Billy, Mar 05 '18 at 16:06
There's some good answers here: https://stackoverflow.com/questions/7070011/writing-large-number-of-records-bulk-insert-to-access-in-net-c — Richard Hansell, Mar 05 '18 at 16:07
Honestly, if you want to bulk copy efficiently, the way to do it is highly dependent on the source and destination database. If you're bulk copying between Access and SQL server (either direction), writing an `INSERT INTO ... SELECT` query is way more efficient (either from Access using ODBC, or from SQL server using `OPENROWSET`). You can almost always use an `INSERT INTO ... SELECT` query with an ODBC source or destination using Access. For SQL server to MySQL, I'd have to check. The question is too broad in my opinion. — Erik A, Mar 05 '18 at 16:16
@RichardHansell I checked out that link and there are some really good answers covering different types of scenarios. Thanks for that. :) — Billy, Mar 05 '18 at 16:20
This is a very contrived exercise from your professor. This kind of thing would never be this loosey goosey in the real world (or at least it shouldn't be). — Sean Lange, Mar 05 '18 at 16:21
@ErikvonAsmuth Yes, that is the issue, the database selected by the user could be MySql to Sql-Server or Sql-Server to Access. It could be any combination, and that's why i'm little unsure if this method is efficient or not. So far i have only tested it with Sql-Server, i don't really know if it'll work for MS-Access or not. But you're right, the question is quite broad. — Billy, Mar 05 '18 at 16:22
@SeanLange Yes, at first i thought it'd be easy to just read from one database and write to other, but as i started coding, turns out there are tons of constraints and hurdles. Plus different database use different type of methods. Its quite overwhelming. — Billy, Mar 05 '18 at 16:24
Note that Access is capable of horrific things, like the following query: `INSERT INTO [Text;FMT={Delimited(;)};HDR=No;DATABASE=C:\SomeFolder].[csvfile#csv](F1) SELECT F1 FROM [ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=localhost;Trusted_Connection=yes;DATABASE=TestDB;].[dbo.Table1]`. You can use Access to move data from one ODBC source to another, or to/from any file that ACE can work with (Excel, text, etc) in a single query without iterating through rows. If you should is another case altogether, but it's pretty efficient. — Erik A, Mar 05 '18 at 17:02

score 1 · Answer 1 · answered Mar 05 '18 at 16:58

I also had a problem similar to yours, I will share the function that I did to solve, I hope it helps you

Public bool InsertIntoDB(string tableName, DataTable _dataTable)
    {
        bool Result = false;
        DataTable ColumnsSQL = GetColumns(tableName);
        string CommandInsert = "Insert Into " + tableName + " (";
        string CommandValue = "";
        string commandSQL = "";
        DateTime Helpconstruct = new DateTime();
        String Word = "";
        String ValueToAdd = ""; 
        string connectionString = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
        FuncAuxC2C HELPPLEASE = new FuncAuxC2C();
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(null, conn);

        for (int k = 0; k < _dataTable.Rows.Count; k++)
        {

            for (int i = 0; i < ColumnsSQL.Rows.Count - 1; i++)
            {
                CommandInsert += ColumnsSQL.Rows[i][0].ToString();

                if (_dataTable.Rows[k][i].ToString() != "")
                {
                    ValueToAdd = _dataTable.Rows[k][i].ToString();
                    if (char.IsNumber(ValueToAdd[0]))
                    {
                        bool haveDigits = false;

                        foreach (char c in _dataTable.Rows[k][i].ToString())
                        {
                            if (c < '0' || c > '9')
                            {
                                haveDigits = true;
                                continue;
                            }

                        }

                        if (haveDigits == true && _dataTable.Rows[k][i].ToString().Contains("e+") && _dataTable.Rows[k][i].ToString().Contains(","))
                        {

                            _dataTable.Rows[k][i] = Decimal.Parse(_dataTable.Rows[k][i].ToString(), System.Globalization.NumberStyles.Float).ToString();

                        }
                    }
                }

                if (_dataTable.Columns[i].ColumnName.ToString() == "Data" && _dataTable.Rows[k][i].ToString() != "")
                {
                    //This is just to format date
                    ValueToAdd = HELPPLEASE.alterDataformat(_dataTable.Rows[k][i].ToString(), "yyyyMMdd", "-");

                }
                else if (_dataTable.Columns[i].ColumnName.ToString() == "tipificacao_datetime" && _dataTable.Rows[k][i].ToString() != "" )
                {
                    //This is just to format date 
                    ValueToAdd = HELPPLEASE.alterDataformat(_dataTable.Rows[k][i].ToString(), "yyyyMMddHHMMSS", "-");
                }
                else
                {
                    if (_dataTable.Rows[k][i].ToString().Length < 250)
                    {
                        ValueToAdd = _dataTable.Rows[k][i].ToString();
                    }
                    else
                    {
                        ValueToAdd = _dataTable.Rows[k][i].ToString().Substring(0, 250);
                    }
                }


                cmd.Parameters.AddWithValue("@" + ColumnsSQL.Rows[i][0].ToString(), SqlDbType.VarChar).Value = ValueToAdd;
                ValueToAdd = "";


                CommandValue += "@" + ColumnsSQL.Rows[i][0].ToString();

                if (ColumnsSQL.Rows.Count - 2 != i)
                {
                    CommandInsert += ", ";
                    CommandValue += ", ";
                }

            }



            commandSQL = CommandInsert + ") VALUES (" + CommandValue + ")";
            try
            {

                conn.Open();
                cmd.CommandText = commandSQL;
                cmd.ExecuteNonQuery();
                conn.Close();
                cmd.Parameters.Clear();

                commandSQL = "";
                CommandValue = "";
                CommandInsert = "";
                CommandInsert = "Insert Into " + tableName + " (";
                Result = true;
            }
            catch (Exception ex)
            {
                conn.Close();
                Result = false;
            }
            finally
            {
                cmd.Parameters.Clear();
                commandSQL = "";
                CommandValue = "";
                CommandInsert = "";
                CommandInsert = "Insert Into " + tableName + " (";
                conn.Close();
            }
        }


        return Result;
    }

}

Hi Joana, your solution seem to be good but i'm not quite sure if some of those methods will work in MS-Access. But, your answer did just help me solve one of the issues i was having, i wasn't clearing the command parameter so program was throwing error after first insert. Now it doesn't. Thanks :) — Billy, Mar 06 '18 at 00:34
I have upvoted this answer, but can't accept it, since it does not addresses the main question "is there a better way?" Nonetheless, it was helpful to me for sure. So thanks for posting it. :) — Billy, Mar 06 '18 at 00:36
In terms of access I only have one application that transforms an Access File into Excel. If it's interesting for you, I can share it with you. :) @Shrey — Joana Brandão, Mar 07 '18 at 14:44

Copying the data of one database table to another

1 Answers1