CODEWITHSUNDEEP
c#mysqlsqldatabasedatabase-design
database
database

Reputation: 23

Copying a large amount of rows between two tables in two different mysql databases

I've got a database, let's call it Database A, and another one called Database B in MySQL.

I have an SQL statement which picks up data from Database A (approximately 1 million rows) and need to insert them into Database B.

To read the data from Database A, I am using a MySqlDataReader which contains the 1 million rows. I would now like to write them to Database B.

What I've tried doing is iterate through each row in the data reader, and write to the database with a simple INSERT stored procedure. This is taking too long however.

I looked into the sql bulk data copy with C#, however I would not like to use external files.

What options do I have?

Upvotes: 2

Views: 208

Answers (1)

Steve
Steve

Reputation: 216363

A simple query like

 INSERT INTO mysqldb1.table1 SELECT * from mysqldb2.table2

This is executed all directly by the MySql engine avoiding any passage of data from MySql and your code

string cmdText = "INSERT INTO mysqldb1.table1 SELECT * from mysqldb2.table2";
using(MySqlConnection cnn = new MySqlConnection(.....))
using(MySqlCommand cmd = new MySqlCommand(cmdText, cnn))
{
    cnn.Open();
    int rows = cmd.ExecuteNonQuery();        
    Console.WriteLine(rows);
}

In a more complex scenario (for example, you need to repeat the operation and the target table initially doesn't exists) you could write a query like this

string cmdText = @"DROP TABLE IF EXISTS mysqldb1.table1;
                   CREATE TABLE mysqldb1.table1 LIKE mysqldb2.table2;
                   INSERT INTO mysqldb1.table1 SELECT * from mysqldb2.table2";
using(MySqlConnection cnn = new MySqlConnection(.....))
using(MySqlCommand cmd = new MySqlCommand(cmdText, cnn))
{
    ......
}

Upvotes: 3

Related Questions