How to Copy Data From One Database to Another in MySQL

1. Copy Data From One Database to Another
2. Copy Data From the MySQL Server to the MS SQL Server
3. Conclusion

We will learn about different ways to copy data from one database to another.

This strategy of copying data is very useful in data replication. Due to data replication, data can be served to different servers and made available to all users without any inconsistency.

The benefits of data replication include increased availability and reliability of data and less communication overhead. We can copy the full or partial database from one database server to another.

In this tutorial, we’ll see how to copy data within MySQL (with the same user), MS SQL Server (with the same user), and from MySQL to MS SQL Server (having different users).

Copy Data From One Database to Another

Using the MySQL Server

We have two databases named students and person in our MySQL Server. The person database has a table named customers, and the students database has a table named student that we want to copy into the person database.

It is important to note that we copy data within one server (MySQL 8.0.27) with one user (root). We have the following databases (see red boxes) and tables (see green boxes).

We want to copy the student table from the students database to the person database using the following SQL query.

#MySQL Version 8.0.27
CREATE TABLE person.students SELECT * FROM students.student; 

Observe the databases and tables after copying the data (see the screenshot below). You can see two tables in the person database, customers and students.

You might have noticed that we wanted to copy the student table from the source database, so why is it named students in the destination database? It is because we named it students in the SQL query.

Using the MS SQL Server

Here, we have two databases named teachers and person in our MS SQL Server. The person database has a table called customers, and the teachers database has a table named teacher that we want to copy into the person database.

We copy data within one server (MS SQL Server) with one user. You can see the current databases (see red boxes) and tables (see green boxes) in the screenshot below.

We’ll copy a table named teacher from the Teachers Database into the Person Database using the following command.

#MSSQL Server
SELECT * INTO Person..teacher FROM Teachers..teacher;

You can see the copied table in the screenshot given below. We now have two tables in the Person database.

Copy Data From the MySQL Server to the MS SQL Server

Have you ever imagined if you have to copy data from your database to another user’s database? It means there are different users involved (and sometimes different machines).

Here we’ll see how to copy data from the MySQL Server to the MS SQL Server (note that there are two users on the same computer, one for MySQL and the other for MS SQL Server).

Right-click on your database in MS SQL Server and select Task -> Import Data.

Click on the Next button in the following screenshot.

In the following screenshot, make sure that the data source is .Net Framework Data Provider for MySQL, write the MySQL database name, port number, and server as students, 3306, and localhost, respectively. Then, click Next.

It is important to note that you may have different database names and Server IP Addresses. We are using two servers on the same machine; that’s why we used localhost.

Enter the username and password of MySQL Server (see the screenshot below) and click Next.

Ensure that destination is selected as SQL Server Native Client 11.0, confirm your server’s name and database name, and select the authentication. We are using Windows Authentication for this tutorial. Then click Next.

Click Next on the following screen.

Write SQL Query as per your requirements; we are copying all records from the student table using the following command. Click Next.

Write your Destination table name (the name that will appear in MS SQL Server) and click Next.

Click Next on the screen given below.

In the following screenshot, select Run Immediately (see green box), or you can save the package and run later (see red box). Click Next.

Click Finish on the following screen.

Here, the following screen shows that the data has been copied. Click on Close.

Check your MS SQL Server for the copied table, and you can see the green box in the following screenshot.

Conclusion

Considering the above discussion, we’ve concluded that copying data from one database to another is important to learn for various reasons, including security concerns, data availability, and reliability, etc. We can also provide access to data to different users worldwide.