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