How to easily import multiple sql files into a MySQL database?

Question

I have several sql files and I want to import all of them at once into a MySQL database.

I go to PHPMyAdmin, access the database, click import, select a file and import it. When I have more than a couple of files it takes a long time.

I would like to know if there is a better way to import multiple files, something like one file which will import the other files or similar.

I'm using WAMP and I would like a solution that does not require installing additional programs on my computer.

Albers

A Script will allow you to do this, but you'll need to learn it. You could write something in php itself or another scripting language such as ruby or python. you could maybe even do it in a batch file. – Transilluminate

Answer 1

In Windows, open a terminal, go to the content folder and write:

copy /b *.sql all_files.sql

This concate all files in only one, making it really quick to import with PhpMyAdmin.

In Linux and macOS, as @BlackCharly pointed out, this will do the trick:

cat *.sql  > .all_files.sql

Important Note: Doing it directly should go well, but it could end up with you stuck in a loop with a massive output file getting bigger and bigger due to the system adding the file to itself. To avoid it, two possible solutions.

A) Put the result in a separate directory to be safe (Thanks @mosh):

mkdir concatSql
cat *.sql  > ./concatSql/all_files.sql

B) Concat them in a file with a different extension and then change it the name. (Thanks @William Turrell)

cat *.sql  > all_files.sql1
mv all_files.sql1 all_files.sql

Albers

what about different table encoding? – Erk

@Alexufo, what do you mean? – Albers

sql dump files may have different encoding. What will be after merging cp1251 and utf 8 in one file? – Erk

If all_files.sql is already present, this can result in recursive copy of the file on itself with no end! Put the result in a separate directory to be safe. – Dewitt

came to Windows after half a decade, and was so lost importing around 100 tables. You just made by day. <b> Thank you </b> – Homemaking

WOW!... thank you so much. i had 500+ tables, and poof! they're imported in a jiffy – Lamont

Problem using XAMMP: when I try to import pages got off with error: "ERR_CONTENT_DECODING_FAILED". Like 1 sec after click on "import" button. – Chaco

Does this traverse files in alphabetical order? It seems to in my mac, but I wasn't sure if that was safe to assume else where – Nika

If you use cat *.sql > all_files.sql you may get (as I did - macOS 10.13.6) stuck in a loop with a massive output file getting bigger and bigger - give it a different extension instead. – Imprimis

Note that this method won't work if the SQL files are UTF-8 and start with a BOM (i.e. the three bytes 0xEF,0xBB,0xBF), then you will get a mysterious "ERROR 1064 (42000) at line 12345: You have an error in your SQL syntax" when importing the concaternated file. – Willow

I did as you said but when I want to import it to phpMyAdmin I get an error that says Incorrect format parameter I'm not sure what is the problem. Can it be because of my huge file? It's 3 GB. – Schultz

how do you do for largers files like 10GB ? – Precipitate

cat *.sql > .all_files.sql if you leave the . (dot) in front it will hide the file in macos – Ostensorium

Answer 2

This is the easiest way that I have found.

In Windows (powershell):

cat *.sql | C:\wamp64\bin\mysql\mysql5.7.21\bin\mysql.exe -u user -p database

You will need to insert the path to your WAMP - MySQL above, I have used my systems path.

In Linux (Bash):

cat *.sql | mysql -u user -p database

Bartie

it may be helpful to add the path to mysql.exe into your windows system path, that way the command for Linux and Windows would be functionally identical. – Ladew

In bad cases, this might give you some trouble with charset, or am I wrong? If the cat command output broken utf-8 characters, those will be piped into your mysql-command, right?! – Weekender

@Weekender Yes, you are right. It will give you an error on the line where the BOM of the next UTF-8-encoded file appears. ("ERROR 1064 (42000) at line nnn: You have an error in your SQL syntax" ) – Willow

Answer 3

1. Goto cmd

2. Type in command prompt C:\users\Usersname>cd [.sql tables folder path ]
   Press Enter
   Ex: C:\users\Usersname>cd E:\project\database

3. Type command prompt
   C:\users\Usersname>[.sql folder's drive (directory)name]
   Press Enter
   Ex: C:\users\Usersname>E:

4. Type command prompt for marge all .sql file(table) in a single file
   copy /b *.sql newdatabase.sql
   Press Enter
   EX: E:\project\database>copy /b *.sql newdatabase.sql

5. You can see Merge Multiple .sql(file) tables Files Into A Single File in your directory folder
   Ex: E:\project\database

Archibaldo

Answer 4

I know it's been a little over two years... but I was looking for a way to do this, and wasn't overly happy with the solution posted (it works fine, but I wanted a little more information as the import happens). When combining all the SQL files in to one, you don't get any sort of progress updates.

So I kept digging for an answer and thought this might be a good place to post what I found for future people looking for the same answer. Here's a command line in Windows that will import multiple SQL files from a folder. You run this from the command line while in the directory where mysql.exe is located.

for /f %f in ('dir /b <dir>\<mask>') do mysql --user=<user> --password=<password> <dbname> < <dir>\%f

With some assumed values (as an example):

for /f %f in ('dir /b c:\sqlbackup\*.sql') do mysql --user=mylogin --password=mypass mydb < c:\sqlbackup\%f

If you had two sets of SQL backups in the folder, you could change the *.sql to something more specific (like mydb_*.sql).

Puduns

This is a great solution, I input more information, in case your folder's name have blank space, we add double quotes. Example: for /f %f in ('dir /b "C:\Bitbucket\db\Aron\March 9\turl_trevin\"*.sql') do mysql --user=root --password=root test < "C:\Bitbucket\db\Aron\March 9\turl_trevin"\%f – Grampus

Answer 5

just type:

cat *.sql |mysql -uroot -p

and mysql will import all the sql file in sequence

Mcvay

Answer 6

You could also a for loop to do so:

#!/bin/bash
for i in *.sql
do
    echo "Importing: $i"
    mysql your_db_name < $i
    wait
done 

Source

Unofficial

Yes, I prefer a loop like this over importing one big sql file, as it is easier to sort out if one or more .sql files fail at importing and then act accordingly. – Oviduct

Answer 7

Enter the mysql shell like this.

mysql --host=localhost --user=username --password --database=db

Then use the source command and a semicolon to seperate the commands.

source file1.sql; source file2; source file3;

Sunburn

Not easy when you have 40 + tables and work with database importing. – Chaco

You could cat them all into one file and problem solved. – Sunburn

Answer 8

Save this file as .bat and run it , change variables inside parenthesis ...

@echo off
title Mysql Import Script
cd (Folder Name)
 for %%a in (*) do (
     echo Importing File  : %%a 
     mysql -u(username) -p(password)  %%~na < %%a
)
pause

if it's only one database modify (%%~na) with the database name .

Modeling

Nice! But what about when there's no password? Like a local db importing for tests...i tried -p() and -p('') without success. – Chaco

I got it like this but always hitting "enter" to informe no password: @echo off title Mysql Import Script for %%a in (*) do ( echo Importing File : %%a C:\xampp\mysql\bin\mysql.exe -u root -p MYDB < %%a ) pause – Chaco

Answer 9

The easiest solution is to copy/paste every sql files in one.

You can't add some sql markup for file importation (the imported files will be in your computer, not in the server, and I don't think MySQL manage some import markup for external sql files).

Acrodont

Yes, it's an easy solution and I knew it, but I didn't want to do it, because I need to keep separated the files: Every file is a portion of my system, and if I want to modify maybe three parts after coding a bit I want to import them without copy and paste. Anyway, a solution if there is no better way, thank you – Albers

An easy way is to create a script concataining all SQL files (e.g. using cat on linux or unix) – Acrodont

Is not easiest. Depending of table sizes, you can get in trouble. – Chaco

Answer 10

Import From multiple SQL files into one Database.

Step 1: Goto to the folder and create file 'import-script.sh' with execute permission (give Permission to file is chmod u+x import-script.sh )

#!/bin/bash
for i in *.sql
do
    echo "Importing: $i"
    mysql -u USERNAME -pPASSWORD DBNAME < $i
    wait
done 

The main thing is -p and PASSWORD didn't add any space.

Step 2: then in your terminal run this command ./import-script.sh

Sapir

Answer 11

in windows open windows powershell and go to the folder where sql files are then run this command

cat *.sql |  C:\xampp\mysql\bin\mysql.exe -u username -p databasename

Aboard

Answer 12

Just type below command on your command prompt & it will bind all sql file into single sql file,

c:/xampp/mysql/bin/sql/ type *.sql > OneFile.sql;

Connolly

Answer 13

for windows users,

You can select the database in the phpMyadmin interface on the left, drag and drop all your files from your windows folder onto the web UI of phpMyadmin.

Selfconceit

Answer 14

puts multiple sql file into one file named as data.sql

cat /path/to/**/*.sql > data.sql

enter into database(I will take a example Postgres, because I'm not so familar with Mysql)

psql -U postgres
\l # list all database
\c database_name # connect database
\i /path/to/data.sql; # insert the data

Adduction