This article primarily for database professionals or enthusiasts with sufficient technical background talks about importing SQL file into MySQL database in an easy-to-understand way.

Additionally, the readers of this article are going to know multiple ways to import a SQL file into their MySQL database.

This article also highlights the importance of using right tools to speed up tasks like importing files and other crucial database related requirements especially when you intend to automate these steps as per business specifications or internal requirements. 

About Importing file into MySQL 

We perform MySQL import SQL (file) for a number of reasons including the following:

  1. We want to populate our MySQL database table using a file-based approach (from a file rather than using a script that runs directly against the target database)
  2. We are interested to construct a demo or test database from a file that contains the entire script to setup the database and its objects along-side populating the tables
  3. We want to merge data from different sources into a single database and one such source is the data available in the file to be imported into MySQL database
  4. We want to perform certain database administrative tasks that are consolidated in a scripted file to be distributed across multiple environments 

Different Types of files that can be imported 

Some of the most commonly used files or data sources that can be imported into MySQL are as follows:

  1. Text
  2. CSV (comma separated)
  3. Excel
  4. XML
  5. JSON
  6. ODBC
  7. Google Sheets
  8. DBF

However, all the above-mentioned types of files must contain the code (the compatible format/structure) required by the MySQL database to understand and interpret such as rows of a table to be imported into a table in a database that already exists in the server.

About MySQL Import SQL file

This is not a very common data importing practice since there are many other ways to achieve the same objective but there may be legitimate reasons to import SQL file into a MySQL database.

Some strong reasoning behind this type of import can be justified in many professional life scenarios including the following:

  1. Suppose you have a business requirement to export MySQL database code from one system to a shared folder (as SQL) followed by importing into another system (MySQL database server) 
  2. You receive SQL generated script from a customer into a shared folder which can then be transferred into the main stream database that you maintain for a customer 
  3. You want to automate SQL based file update for a highly dynamic transactional table then you need to import SQL into the MySQL database 
  4. You are limited by a MySQL database system that can only flush (dump) SQL file to you that can be then imported into the target MySQL database for update and maintenance purposes.
  5. There may be special requirements to import SQL back into the MySQL database

Tools/Approaches to Import SQL into MySQL database

There are multiple ways to import a SQL file into MySQL database while you can also use proprietary database tools for ease of use to achieve such objectives particularly when time and effort must be optimised and automation is the preferred way of working in a busy high in demand professional environment. 

We can import SQL file in the following ways:

  1. Using Command Prompt to import SQL file into the target MySQL database
  2. Using proprietary database development tools like dbForge Studio for MySQL

Prerequisite(s)

This article assumes the following:

  1. The readers interested to run the examples are familiar with basics of database scripting
  2. MySQL is already installed on your machine (if not then please check MySQL official website or click the download link)
  3. MySQL has already been configured locally and the server is running (while you know the configuration sensitive information including the root password)

dbForge Studio for MySQL 

Please note that I am using dbForge Studio for MySQL in this article for most of the demonstration including main stream example to cover a professional life scenario but the examples that require only MySQL command line client can work even if you have not installed this tool with the exception of the final example that requires this tool to be installed. 

Handling SQL Files and DbForge Studio for MySQL 

Please note that the files in SQL format can be of a lot more different than one another as they can contain database/table creation scripts on one side while they may have statements like INSERT, UPDATE, REPLACE on the other side. The Studio knows how to handle and process these files with different content. 

Setup a Target Demo Database (itsalesdemo)

Before we jump off to any further implementation, we first need to setup a target demo database on MySQL local server.

This target database is going to be used to import the SQL file that contains data for the existing table of the database.

Let us create a demo database called “itsalesdemo” by opening dbForge Studio for MySQL and clicking on Database followed by New Database from the main menu as shown below:

Let us name this database as itsalesdemo and click Apply Changes:

Refresh the database explorer to see the newly setup demo database:

This is an empty database so far so we need to add a table called monthlysale table:

Alternatively, you can add a new object (table) by using the following script against the demo database:

CREATE TABLE itsalesdemo.monthlysale (

  SaleId int NOT NULL AUTO_INCREMENT,

  SellingDate datetime DEFAULT NULL,

  Customer varchar(255) DEFAULT NULL,

  Product varchar(255) DEFAULT NULL,

  TotalPrice decimal(10, 2) DEFAULT NULL,

  PRIMARY KEY (SaleId)

)

ENGINE = INNODB,

AUTO_INCREMENT = 4,

CHARACTER SET utf8mb4,

COLLATE utf8mb4_0900_ai_ci;

View the monthlysale Table Data

Right click on the table and click on Retrieve Data to view the rows of the monthly sale table:

The output is empty:

Place SQL file with one row in a Common Shared Folder

Let us now create a SQL file with the following script that contains one new record for the table monthlysale:

— Row 1 to be imported into monthlysale table

INSERT INTO monthlysale(SaleId, SellingDate, Customer, Product, TotalPrice) VALUES

(1, ‘2022-01-01 10:00:00’, ‘Asif’, ‘Black Laptop’, 230.00);

Save the file as SaleData01.sql and put it in an accessible location on your machine such as C:\SQLShare:

Using Command Prompt to import SQL file into the target MySQL database

Now please open command prompt by pressing Window+R and typing cmd as follows:

Next please locate the bin folder under MySQL by issuing the following command and pressing enter key:

cd\program files\mysql\mysql server 8.0\bin 

The output should be as follows:

Now we need to run a command to import SQL file that we placed earlier in SQLShare folder into our demo target database that contains a new record for the table MonthlySale.

Please type the following command followed by pressing enter key:

Mysql -u root -p itsalesdemo < “C:\SQLShare\SaleData01.sql”

Here u is for the username which is root while p is for the password to be asked next and itsalesdemo is the target database while on the right side is the location of the SQL file to be imported for the target database.

Enter the root user password you setup at the time of configuring MySQL:

Once this is done successfully, please go back to the table view (Retrieve Data) in dbForge Studio for MySQL and click refresh:

Now add one more file called SaleData02.sql that contains the following script:

— Row 2 to be imported into monthlysale table in ITSalesDemo database

INSERT INTO monthlysale(SaleId, SellingDate, Customer, Product, TotalPrice) VALUES

(2, ‘2022-01-02 10:00:00’, ‘Peter’, ‘Blue Laptop’, 250.00);

Place the file in the same folder:

Run the following command in command prompt:

Mysql -u root -p itsalesdemo < “C:\SQLShare\SaleData02.sql”

Refresh the table:

We have imported second row from the sql file into the desired table of our database now.

MySQL Import SQL file using dbForge Studio for MySQL

This can be really helpful if we want to automate the process because dbForge Studio for MySQL also gives us the ability to create command line script for the actions we do to import data into the desired database table.

We can use dbForge to achieve the following purposes (but not limited to):

  1. Export data from source system (database) into SQL using dbForge Studio for MySQL and using the MySQL command prompt method to import data into another database
  2. Importing SQL file as a restore database option and then automating the process

Let us discuss how to import SQL file using restore database option in dbForge Studio for MySQL.

Reset monthly sale Table

Right click on monthlysale table and click on Truncate table to remove all the data from the table:

Refresh the data of the table as there won’t by any more rows now:

Please choose Restore Database option as shown below:

Select SaleData01.sql file and click on Restore:

The following output tells you the status of your restore:

Refresh the table:

We have successfully inserted a row from a SQL file into the table.

Please note that by rerunning the restore process against SaleData02.sql will add second row to the table.

It is worth mentioning that the BACKUP/RESTORE option of the studio can handle a full database copy, a table copy or a data dump.

Automating the Import Process using dbForge Studio for MySQL 

Finally, during the restore process you get an opportunity to save command line statement and this is your way to automate the process (by using the command line statement in scheduled task to run automatically as when needed).

Let us revisit the Database Restore Wizard and after selecting one of the SQL (script) import click on Save Command Line… in dbForge Studio for MySQL:

This will instantly give you the command line version of the restore SQL option which can also save it in a safe location:

Once the file is saved successfully. Please open up task scheduler

Create a basic task by naming it as “AutomateImportSQLSales”:

Add the restore script (saved earlier) as new Action and click OK:

Once it is added to scheduler task simply schedule it to run on daily or weekly basis as per your requirements.

Tool Tip

Please remember that dbForge Studio for MySQL offers running large SQL scripts without loading into memory which is a very handy feature particularly when dealing with big SQL data scripts (for import) purposes:

Congratulations! You have successfully learned multiple ways to import SQL file into the MySQL database keeping in mind the tips to use in professional life scenarios.

Things to do

Now that you are familiar with MySQL import SQL file successfully, please try the following to improve your skills

  1. Please try to add hundred and thousands of rows by importing a SQL file to see if the performance degrades with more data or not 
  2. Keeping in mind this article please import multiple SQL files from multiple places into a single table
  3. Try automating the SQL import method by using command line script generated by dbForge Studio for MySQL and scheduling it as a windows task.

Also Read: Top 10 plugins which every WordPress website must have in 2015

Post Views: 45