JIYIK CN >

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

This strategy of replicating data 数据复制is very useful in . 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 data availability and reliability and reduced communication overhead. We can replicate a complete or partial database from one database server to another.

In this tutorial, we will learn how to copy data between MySQL (using same user), MS SQL Server (using same user), and from MySQL to MS SQL Server (using different users).

Copy data from one database to another

Using MySQL Server

We have two databases named studentsand in our MySQL server . The database has a table named and the database has a table named that we want to copy to the database.personpersoncustomersstudentsstudentperson

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

We want to copy studenta table from studentsthe database to personthe database using the following SQL query.

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

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

You may have noticed that we want to copy the table from the source database student, so why is it named as in the target database students? This is because we named it as in the SQL query students.

Using MS SQL Server

Here, we have two databases named teachersand in our MS SQL Server. The database has a table named and the database has a table named, which we want to replicate to the database.personpersoncustomersteachersteacherperson

We are replicating data in one server (MS SQL Server) with one user. You can see the current database (see red box) and table (see green box) in the screenshot below.

We will use the following command to copy teachera table named from the database to the database.TeachersPerson

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

You can see the copied tables in the screenshot given below. We now Personhave two tables in the database.

Copy data from MySQL server to MS SQL server

Have you ever thought about what would happen if you had to copy data from your database to another user's database? This means different users (and sometimes different machines) are involved.

Here we will see how to copy data from MySQL server to MS SQL server (note that there are two users on the same machine, one for MySQL and another for MS SQL Server).

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

Click the Next button as shown in the following screenshot.

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

It is important to note that you may have different database names and server IP addresses. We use two servers on the same machine; that is localhostwhy we use .

用户名Enter the sum of your MySQL server 密码(see screenshot below) and click 下一步.

Make sure Target is selected SQL Server Native Client 11.0, confirm your server name and database name, and select Authentication. We are using Windows Authentication in this tutorial. Then click Next.

Click Next on the following screen.

Write the SQL query as per your requirement; we are using the following command studentto copy all the records from the table. Click Next.

Write down your target 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 立即运行(see green box), or you can save the package and run it later (see red box). Click Next.

Click Finish on the following screen.

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

Check your MS SQL Server for the replicated tables, you can see the green boxes in the following screenshot.

in conclusion

Considering the above discussion, we conclude that it is important to replicate data from one database to another for various reasons including security issues, data availability, reliability, etc. We can also provide access rights to the data to different users across the globe.