{"id":12836,"date":"2023-12-10T20:22:51","date_gmt":"2023-12-11T03:22:51","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12836"},"modified":"2023-12-10T20:26:12","modified_gmt":"2023-12-11T03:26:12","slug":"execute-sql-file-in-mysql","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/execute-sql-file-in-mysql\/","title":{"rendered":"How to Execute an SQL File in MySQL"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn various ways to execute an SQL file in MySQL using the mysql command line interface, the source command, and the MySQL Workbench.<\/p>\n\n\n\n

Running an SQL file in MySQL is an essential skill for database administrators and developers. MySQL offers serval ways to execute an SQL file.<\/p>\n\n\n\n

Executing a SQL file using MySQL command-line interface<\/h2>\n\n\n\n

First, open the Command Prompt on Windows or the Terminal on macOS or Linux.<\/p>\n\n\n\n

Second, run the following command:<\/p>\n\n\n

mysql -u username -p database_name < path\/to\/your\/sqlfile.sql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this command, you replace the username with your MySQL username, database_name<\/code> with the target database and path\/to\/your\/sqlfile.sql<\/code> with the actual path to your SQL file.<\/p>\n\n\n\n
The command will prompt you to enter a password for the username. After providing the password, you press the Enter key to execute.<\/p>\n\n\n\n
We’ll create a new database<\/a> called sales<\/code> for the demonstration.<\/p>\n\n\n\n
First, connect to the MySQL server:<\/p>\n\n\n
mysql -u root -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a new database called sales<\/code>:<\/p>\n\n\n
CREATE<\/span> DATABASE<\/span> sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, exit the mysql:<\/p>\n\n\n
exit<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Create a new file called script.sql<\/code> with the following code and save it to the c:\\temp<\/code> directory:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> products(\n   id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n   product_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n   price DECIMAL<\/span>(10<\/span>,2<\/span>) NOT<\/span> NULL<\/span>\n);\n\nINSERT<\/span> INTO<\/span> products (product_name, price) \nVALUES<\/span>\n('Smartphone XYZ'<\/span>, 599.99<\/span>),\n('Laptop ABC'<\/span>, 1299.99<\/span>),\n('Wireless Earbuds'<\/span>, 79.99<\/span>),\n('4K Ultra HD Smart TV'<\/span>, 899.99<\/span>),\n('Gaming Console XYZ'<\/span>, 399.99<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To execute the script.sql<\/code> file, you use the following command:<\/p>\n\n\n
mysql -u root -p sales < c:\/temp\/script.sql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To verify the execution  you can follow these steps:<\/p>\n\n\n\n
First, connect to the sales<\/code> database:<\/p>\n\n\n
mysql -u root -p -D sales<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+-----------------+<\/span>\n| Tables_in_sales |\n+-----------------+<\/span>\n| products        |\n+-----------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
select<\/span> * from<\/span> products;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+----------------------+---------+<\/span>\n| id | product_name         | price   |\n+----+----------------------+---------+<\/span>\n|  1 | Smartphone XYZ       |  599.99 |\n|  2 | Laptop ABC           | 1299.99 |\n|  3 | Wireless Earbuds     |   79.99 |\n|  4 | 4K Ultra HD Smart TV |  899.99 |\n|  5 | Gaming Console XYZ   |  399.99 |\n+----+----------------------+---------+<\/span>\n5 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Executing an SQL file using the source command<\/h2>\n\n\n\n
To execute an SQL file using the source command, you follow these steps:<\/p>\n\n\n\n
First, connect to the MySQL server using the mysql client program:<\/p>\n\n\n
mysql -u root -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, switch to a target database e.g., sales<\/code> where you want to execute the SQL statements:<\/p>\n\n\n
use<\/span> sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, execute the source command:<\/p>\n\n\n
source path\/to\/your\/sqlfile.sql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, you can create a new file called script2.sq<\/code> in the C:\\temp directory with the following code:<\/p>\n\n\n
-- Create the customers table<\/span>\nCREATE<\/span> TABLE<\/span> customers (\n   id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n   customer_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n   email VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n);\n\n-- Insert five customers into the customers table<\/span>\nINSERT<\/span> INTO<\/span> customers (customer_name, email) VALUES<\/span>\n('John Doe'<\/span>, 'john.doe@example.com'<\/span>),\n('Jane Smith'<\/span>, 'jane.smith@example.com'<\/span>),\n('Alice Johnson'<\/span>, 'alice.johnson@example.com'<\/span>),\n('Bob Williams'<\/span>, 'bob.williams@example.com'<\/span>),\n('Eva Davis'<\/span>, 'eva.davis@example.com'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following source<\/code> command executes the script2.sql<\/code> file:<\/p>\n\n\n
source c:\/temp\/script2.sql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The command returns the following output:<\/p>\n\n\n
Query OK, 0 rows affected (0.02 sec)\n\nQuery OK, 5 rows affected (0.00 sec)\nRecords: 5  Duplicates: 0  Warnings: 0<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following show tables statement displays all tables in the sales<\/code> database to verify the execution:<\/p>\n\n\n
show<\/span> tables<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-----------------+<\/span>\n| Tables_in_sales |\n+-----------------+<\/span>\n| customers       |\n| products        |\n+-----------------+<\/span>\n2 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Executing an SQL file using MySQL workbench<\/h2>\n\n\n\n
To execute an SQL file using MySQL Workbench, you follow these steps:<\/p>\n\n\n\n
    \n
  1. Open MySQL Workbench and connect to your MySQL server.<\/li>\n\n\n\n
  2. Navigate to the “File” menu, select “Open SQL Script,” and choose your SQL file.<\/li>\n\n\n\n
  3. Click the lightning bolt icon or use the shortcut (Ctrl + Enter) to execute the script.<\/li>\n<\/ol>\n\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Utilize the mysql command-line interface mysql -u username -p database_name<\/code> < path\/to\/your\/sqlfile.sql<\/code>to execute an SQL file.<\/li>\n\n\n\n
    • Leverage the source command to execute an SQL file.<\/li>\n\n\n\n
    • Use the MySQL Workbench to execute an SQL file.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t
Third, retrieve data<\/a> from the products<\/code> table:<\/p>\n\n\n
Second, show all the tables<\/a> in the sales<\/code> database:<\/p>\n\n\n