How to import a single table in to mysql database using command line

Question

I had successfully imported a database using command line, but now my pain area is how to import a single table with its data to the existing database using command line.

See also this duplicate question: http://stackoverflow.com/q/1013852/385571 with its more relevant answers — MattBianco, Apr 29 '16 at 13:53
Possible duplicate of [Can I restore a single table from a full mysql mysqldump file?](https://stackoverflow.com/questions/1013852/can-i-restore-a-single-table-from-a-full-mysql-mysqldump-file) — codeforester, Oct 27 '18 at 00:01

score 365 · Answer 1 · edited Feb 24 '23 at 21:13

365

Linux :

In command line

 mysql -u username -p databasename < path/example.sql

put your table in example.sql

Import / Export for single table:

Export table schema
```
mysqldump -u username -p databasename tableName > path/example.sql
```
This will create a file named example.sql at the path mentioned and write the create table sql command to create table tableName.
Import a single table into database
```
mysql -u username -p databasename < path/example.sql
```
This command needs an sql file containing data in form of insert statements for table tableName. All the insert statements will be executed and the data will be loaded.

edited Feb 24 '23 at 21:13

Stephen P

14,422
2
43
67

answered Mar 22 '11 at 06:32

sush

5,897
5
30
39

3

the -u was missed ;) mysqldump -u username -p databasename tableName > path/example.sql – Kazmin Dec 01 '14 at 10:27
2

For importing you don't need to use mysql dump. mysql -u username -p databasename tableName < path/example.sql It should do your job – Prabhakar Apr 06 '15 at 10:54
8

The `tableName` name does not needed for all MySQL versions and it produces errors, so you may need to omit it! – mchar Aug 25 '16 at 10:35
1

I was getting a syntax error until I realized to be in bash and not mysql when executing. – Christia Jan 06 '17 at 02:01
2

This would work only if I have access to the database from which I can export a specific table. However, if I have a full dump of the database and want to cherry-pick the table(s) to be imported, this solution won't work. The linked post [Can I restore a single table from a full mysql mysqldump file?](https://stackoverflow.com/q/1013852/6862601) addresses that issue. – codeforester Oct 27 '18 at 00:00
For importing table following is worked for me. `mysql -u username -p -D dbname < tableName.sql` – wahid Sep 29 '19 at 09:27

score 47 · Answer 2 · 2017-04-05T15:15:08.267

47

Export:

mysqldump --user=root databasename > whole.database.sql
mysqldump --user=root databasename onlySingleTableName > single.table.sql

Import:

Whole database:

mysql --user=root wholedatabase < whole.database.sql

Single table:

mysql --user=root databasename < single.table.sql

edited Apr 05 '17 at 15:15

answered Aug 04 '13 at 20:46

score 23 · Answer 3 · edited Aug 14 '20 at 16:39

23

Importing the Single Table

To import a single table into an existing database you would use the following command:

mysql -u username -p -D database_name < tableName.sql

Note:It is better to use full path of the sql file tableName.sql

edited Aug 14 '20 at 16:39

HoldOffHunger

18,769
10
104
133

answered Dec 03 '14 at 06:41

Tanmay Patel

1,770
21
28

score 6 · Answer 4 · edited Sep 16 '19 at 08:35

First of all, login to your database and check whether the database table which you want to import is not available on your database.

If it is available, delete the table using the command. Else it will throw an error while importing the table.

DROP TABLE Table_Name;

Then, move to the folder in which you have the .sql file to import and run the following command from your terminal

mysql -u username -p  databasename  < yourtable.sql

The terminal will ask you to enter the password. Enter it and check the database.

score 5 · Answer 5 · answered Apr 06 '15 at 12:27

5

Command Line

Import / Export for single table:

Exporting table schema

 -> mysqldump -u your_user_name -p your_database_name table_name > test.sql

This will create a file named test.sql and creates table sql command to create table table_name.

Importing data into table

 -> mysql -u your_user_name -p database_name table_name < test.sql

Make sure your test.sql file is in the same directory, if not navigate through the path and then run the command.

answered Apr 06 '15 at 12:27

Prabhakar

6,458
2
40
51

3

You do not need to specify the table name in the command line, as the exported SQL file has the relevant call to create the table. All you need is `mysql -u your_user_name -p database_name < test.sql`. – Michael De Silva Apr 09 '15 at 11:12
1

Here's an example from an exported table, ` 22 DROP TABLE IF EXISTS `account_product_prices`; 23 /*!40101 SET @saved_cs_client = @@character_set_client */; 24 /*!40101 SET character_set_client = utf8 */; 25 CREATE TABLE `account_product_prices` (` – Michael De Silva Apr 09 '15 at 11:13

score 4 · Answer 6 · answered Feb 21 '13 at 07:45

4

It works correctly...

C:\>mysql>bin>mysql -u USERNAME DB_NAME < tableNameFile.sql

please note .sql file specified your current database..

answered Feb 21 '13 at 07:45

Ranjith

2,779
3
22
41

score 3 · Answer 7 · answered Mar 04 '15 at 13:53

3

We can import single table using CMD as below:

D:\wamp\bin\mysql\mysql5.5.24\bin>mysql -h hostname -u username -p passowrd databasename < filepath

answered Mar 04 '15 at 13:53

Digisha

355
3
5

score 3 · Answer 8 · answered Oct 01 '20 at 03:42

If you're in the pwd of an SQL dump and you need a table from that, do this:

sed -n '/-- Table structure for table `'TableNameTo_GrabHere'`/,/-- Table/{ /^--.*$/d;p }' dump_file_to_extract_from.sql > table_name_here.sql

Then just import the table you extracted from the above into the needed database

score 2 · Answer 9 · edited Dec 13 '18 at 10:51

2

Also its working. In command form

cd C:\wamp\bin\mysql\mysql5.5.8\bin //hit enter
mysql -u -p databasename //-u=root,-p=blank

edited Dec 13 '18 at 10:51

Agilanbu

2,747
2
28
33

answered Apr 11 '14 at 11:58

money

149
5

score 2 · Answer 10 · answered Nov 09 '16 at 09:56

2

you can do it in mysql command instead of linux command.
1.login your mysql.
2.excute this in mysql command:
use DATABASE_NAME;
SET autocommit=0 ; source ABSOLUTE_PATH/TABLE_SQL_FILE.sql ; COMMIT ;

answered Nov 09 '16 at 09:56

GeekLei

991
7
8

score 2 · Answer 11 · answered Nov 12 '18 at 10:31

2

if you already have the desired table on your database, first delete it and then run the command below:

 mysql -u username -p  databasename  < yourtable.sql

answered Nov 12 '18 at 10:31

jsina

4,433
1
30
28

score 2 · Answer 12 · edited Dec 04 '18 at 11:51

2

From server to local(Exporting)

mysqldump -u username -p db_name table_name > path/filename.sql;
mysqldump -u root -p remotelab welcome_ulink > 
/home_local/ladmin/kakalwar/base/welcome_ulink.sql;

From local to server(Importing)

mysql -u username -p -D databasename < path/x/y/z/welcome_queue.sql
mysql -u root -p -D remotelab < 
/home_local/ladmin/kakalwar/instant_status/db_04_12/welcome_queue.sql

edited Dec 04 '18 at 11:51

Agilanbu

2,747
2
28
33

answered Dec 04 '18 at 10:42

Venkatesh Kakalwar

21
2

2

For beginers please note u have to operate from terminal not inside the mysql after hitting the command it will ask for the password the db_password – Venkatesh Kakalwar Dec 04 '18 at 10:44

score 2 · Answer 13 · edited Dec 13 '18 at 13:07

2

It would be combination of EXPORT INTO OUTFILE and LOAD DATA INFILE
You need to export that single table with EXPORT INTO OUTFILE, this will export table data to a file. You can import that particular table using LOAD DATA INFILE
Refer doc1 , doc2

edited Dec 13 '18 at 13:07

Agilanbu

2,747
2
28
33

answered Mar 22 '11 at 06:56

Shamit Verma

3,839
23
22

Nids Barthwal · Answer 14 · 2022-12-29T05:58:15.037

1

To import a particular table in database follow below command. Here table_name.sql is dump of taht particular table that you are going to import

mysql -u root -p database_name table_name < table_name.sql

To export a particular table from database follow below command.

mysqldump -u root -p database_name table_name > table_name.sql

edited Dec 29 '22 at 05:58

answered Mar 21 '17 at 11:23

Nids Barthwal

2,205
20
12

1

Whilst this code snippet is welcome, and may provide some help, it would be [greatly improved if it included an explanation](//meta.stackexchange.com/q/114762) of *how* and *why* this solves the problem. Remember that you are answering the question for readers in the future, not just the person asking now! Please [edit] your answer to add explanation, and give an indication of what limitations and assumptions apply. – Toby Speight Mar 21 '17 at 14:16

score 0 · Answer 15 · edited Dec 10 '19 at 07:16

0

-> mysql -h host -u user -p database_name table_name < test_table.sql

edited Dec 10 '19 at 07:16

4b0

21,981
30
95
142

answered Dec 10 '19 at 07:12

Kesha Viveki

169
2
2

the table_name param is not available in latest mysql versions – Marco Marsala May 14 '21 at 06:45

score 0 · Answer 16 · answered Dec 17 '19 at 23:21

Using a temporary database could be a solution depending on the size of the database.

mysql -u [username] -p -e "create database tempdb"
mysql -u [username] -p tempdb < db.sql
mysqldump -u [username] -p tempdb _table_to_import_ > table_to_import.sql
mysql -u [username] -p maindb < table_to_import.sql

score 0 · Answer 17 · answered Apr 18 '20 at 14:22

To import a table into database, if table is already exist then add this line in your sql file DROP TABLE IF EXIST 'table_name' and run command mysql -u username -p database_name < import_sql_file.sql. Please verify the sql file path before execute the command.

score 0 · Answer 18 · answered Mar 15 '22 at 11:46

0

Open the backup file in the VScode and search the table name copy the create table and insert command for the table. Copy and execute those two commands in the database where it is required.

answered Mar 15 '22 at 11:46

Mahesh Shivaiah

1
2

score 0 · Answer 19 · answered Jul 28 '22 at 17:51

0

Use the below command to import a single table into the database on RDS

mysql -h rds_end_point -u username -p databasename < example.sql

answered Jul 28 '22 at 17:51

Ninja Man

86
3

score -1 · Answer 20 · answered Mar 17 '21 at 04:18

First of all take backup of your both database, step 2 select table which you want to export now select export button now download sql file now you have to import into another database simply select database and then import sql file ... simple and easy.

How to import a single table in to mysql database using command line

20 Answers20

Linked