How to insert thousands of records in a MySQL table?

MySQL Tutorials

In this article, we will discuss insert thousands of records in a MySQL table. What steps we should follow while inserting thousands of records. what kind of problem arises while doing these kinds of tasks.

So let’s discuss in details

If you are trying to insert in a table which already having records, And it is important to you then please take a backup to that table first.

Take a backup of that table

Dump

mysqldump db_name table_name > table_name.sql

Dumping from a remote database

mysqldump -u <db_username> -h <db_host> -p db_name table_name > table_name.sql

Turn Off Auto Commit

After taking a backup the next thing you should do is to off auto-commit in MySQL

SET autocommit=0;

After running this command everything is now in your control if everything is OK according to you then do COMMIT manually if not then ROLLBACK.

COMMIT;

OR

ROLLBACK;

Turn Off Foreign Key Check

If there is a Foreign Key Constraint in your table then it will better to turn it off if you are sure you are going to make valid entry through your bulk data insert process. It will make this fast.

SET foreign_key_checks=0;
SET foreign_key_checks=1;

Now you are ready to insert thousands of records in a MySQL table

For loading data in bulk, you must insert it using a text file.

First, write all the SQL statement in that text file.

Then use the command:

mysql db_name < text_file

 

And you must use INSERT IGNORE statements instead of an INSERT statement. So that this process will not reproduce unique check error while inserting data through the text file into the MySQL Table.

To know about INSERT IGNORE STATEMENT read the article here

And to know about INSERT STATEMENT read the article here

Otherwise, you can turn off unique check-in MySQL using below command

SET unique_checks=0; #to turn off unique check
SET unique_checks=1; #to turn on unique check

After taking completing all the process. Please activate auto-commit after committing all your changes.

SET autocommit=1;

If you want to restore the table from the backup file, you can use

mysql -u username -p db_name < /path/to/table_name.sql