CODEWITHSUNDEEP
c#mysqlsql.netdatabase
Dot NET
Dot NET

Reputation: 4907

How to import data from one database to another

I've got an SQL Statement running in C#, which fetches data from a table in Database A in MySQL, and inserts it into a table in Database B.

I am carrying out the select and insert in one SQL statement, as the data is too much to hold in memory. My question is, how would I specify the connection string of the destination database (Database B).

My SQL statement is like so:

sql = INSERT INTO Database_B.Table (SELECT * FROM Database_B.Table);

As it is right now, I am specifying the connection string of Database A only:

sourceDatabaseConnectionString = "Server=localhost;Port=3306;Database=Database_A;Uid=root;Pwd=root;";

using (MySqlConnection connection = new MySqlConnection(sourceDatabaseConnectionString))
{
    connection.Open();

    using (MySqlCommand command = new MySqlCommand(sql, connection))
    {
        command.CommandTimeout = 0;
        command.ExecuteNonQuery();
    }
}

Now the thing is that this is working perfectly. But both databases are on the same server and have the same credentials. How would I handle it if the database is placed elsewhere?

Upvotes: 2

Views: 6792

Answers (2)

Dot NET
Dot NET

Reputation: 4907

I have solved this issue by using OUTFILE syntax. It was the only way I could think of solving this without using linked servers, which is not practical for my situation, since I do not have access over the databases, and just accept connection strings from users.

What my solution does is first carry out a SELECT statement and write the data to a file. Next my INSERT statement picks up from the file, and inserts into another table.

This approach thus avoids putting millions of records in memory, and since they are two separate statements, I am able to bind each one with different connection strings - hence problem solved :)

The code is similar to this:

statement1 = SELECT myFields INTO OUTFILE @file;

statement2 = LOAD DATA LOCAL INFILE @file INTO TABLE myTable;

Upvotes: 0

Paul Weiland
Paul Weiland

Reputation: 737

I quote the post of Mahmoud Gamal who suggested this answer in another post:

Use SELECT ... INTO ... with a fully qualified table names database.schema.object_name, like this:

 USE DatabaseName;    
 SELECT * FROM DatabaseName.schemaname.Tablename INSERT INTO
 AnotherDatabase.schemaname.AnotherTablename;

Then you can use ADO.net or other API to execute this query from C#.

Source: Inserting result of a SQL query from one database to another in C#

Upvotes: 1

Related Questions