Nisarg Upadhyay
choose data source (Columns tab)

Export data from MySQL tables to Azure SQL database

August 12, 2020 by

In this article, I am going to explain how we can export the data of the on-premise MySQL database to Azure SQL database. This task can be performed in two phases.

  1. Export the data of MySQL table in CSV files using MySQL Workbench
  2. Import the CSV file in Azure SQL database using SQL Server Import/Export wizard

First, let us import data from the MySQL Table to the CSV files. To do that, open MySQL Workbench Connect to the MySQL Server From the left pan, expand the sakila database Expand Tables Right-click on any Table click on Table Data Export Wizard.

Table data export wizard

A Table Data Export wizard opens, on the “Select data for export” screen, choose the desired table from the Select source table for export drop-down table. Click on Next.

Select Table

On select output location screen, provide the location of the file in the text box. You can export table data in JSON or CSV files. We will export data in CSV format, hence select CSV. You can specify the format of CSV files. Details are the following:

  1. Line separator: The valid options are LF (Line feed), CR (Carriage return), and CR LF (both)
  2. Enclose strings in: You can specify the character in which you want to enclose the string
  3. Field separator: You can specify the field separator character from the drop-down box
  4. NULL or NULL word as a SQL Keyword: If you want to use the value of a NULL or NULL keyword, then specify Yes from the drop-down box

Change the options according to the requirement and click on Next.

Provide destination file

On the export data screen, you can view the list of tasks to be performed. Click on Next to perform the export. You can see the status of the task in the message box.

Export completed

The exported CSV file looks like the following image:

CSV file

Import the CSV file in Azure SQL database using SQL Server Import/Export wizard

Now, let us import the ActorTable.csv file to the Actor table of the sakila database. To do that, we are going to use the SQL Server Import and Export wizard (64 bit). Launch SQL Server Import and Export Wizard.

On the first screen, just like all other wizards, you can see the details of the wizard. You can review it and click on Next. Below is the screenshot:

SQL Server Import and export wizard

On the Choose a Data Source screen, you can choose the data source from which you can copy the data. We are going to copy the data from the CSV files, hence choose the Flat File Source from the Data Source drop-down box. You must specify the location of the CSV file. To do that, click on Browse. Locate and select the file and click OK. Once the file is loaded, the format options will be set up automatically.

Choose the data source

Click on columns from the left pan to view the list of the column and data. Suppose, while exporting the data, the row delimiter and column delimiter is different than the default values. You can change it from row delimiter and column delimiter drop-down box.

choose data source (Columns tab)

On the preview tab, you can view the data. If the table structure and data are correct, then click Next.

choose data source (Preview tab)

On the Choose a Destination screen, you must specify the destination where you want to copy the data. We want to copy the data to the Azure SQL database, hence choose SQL Native Client 11.0 from the destination drop-down box. Specify the destination server name in Server Name drop-down box. We are copying data to the Azure SQL database; hence we must use SQL Server authentication; therefore, select SQL Server authentication. Provide an appropriate user name and password to connect to the Azure SQL database. Choose the name of the destination database from the database drop-down box. Click Next.

Choose the destination

On the Select Source Tables and Views screen, we can perform mappings between columns of source and destination. To edit the mappings, click on the Edit Mappings button.

Select source tables and views

A dialog box, column mapping opens. On this dialog box, you can do the following changes:

  1. Create a table in the destination database that has the same column structure as the source table. Also, you can recreate the same table
  2. Enable Identity insert
  3. Change the data type of the destination table or change the attributes of the tables
  4. You can perform mappings of the column of the source table to the destination table

The following is the screenshot:

Column mappings

Click on Next. On the Run Package screen, enable the option “Run the package now” and click on Finish. On the Complete the Wizard screen, review the list of activities to be performed by it and click on Finish. See the following image:

View actions

If the wizard completes without error, you can view the number of the rows that have been transferred to the destination. Click OK to close the window.

Export completed

To verify that the data has been exported without error, connect to the SQL Server management studio Connect to the Azure SQL Server Run the following query on the sakila database.

The following is the output:

Table has been copied to Azure SQL Database

As you can see that all rows of the actor table have been copied to Azure SQL database.

Summary

In this article, I have explained how we can export the data of the MySQL database to a CSV file and from the CSV file to Azure SQL database using SQL Server import-export wizard.

Nisarg Upadhyay
Latest posts by Nisarg Upadhyay (see all)
Azure, Importing, exporting, Migration, MySQL

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

177 Views