How to make bulk inserts in MySQl

How to insert rows from another table in MySql and how to handle duplicates.

Manual data

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.

Ignore duplicates

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

Handle duplicates

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
``