How to make bulk inserts in MySQl
How to insert rows from another table in MySql and how to handle duplicates.
If you want to add manually data into a table you may use the keyword VALUES:
INSERT INTO orders (customer_id, 'town') VALUES (3, 'Berlin'), (4, 'New York')
Data from other table
If you want to add a bulk insert from another table, you shall not use the keyword VALUES:
INSERT INTO orders (customer_id, town) SELECT id, customer_town FROM other_table
If you want to add a bulk insert with the possibility that rows already exists (who have to be unique because of a unique column constrain) than the insert would not take place at all, if there is one duplicate row. T his is because MySQL wraps single insert/update/delete commands in a transaction.
If you would like to bulk insert all rows except the duplicate ones, then you can do it by using the IGNORE keyword:
INSERT IGNORE INTO orders (customer_id, town) SELECT id, customer_town FROM other_table
Assume there is a unique constrain on the customer_id but you would like to update the town on duplicate, then you can do it like this:
INSERT INTO orders (customer_id, town) SELECT id, customer_town FROM other_table ON DUPLICATE KEY UPDATE town = other_table.customer_town ``