Mysql: How to insert a record for each result in a SQL query?

Enhanced bulk insertion

Boiling down INSERT INTO ... SELECT, we encounter certain flavours that can spice up your operation. Let's take it up a notch to make sure your insertions are both savory and crisp.

Embracing uniqueness

To oust duplicates, the DISTINCT keyword is your knight in shining armor.

-- No more Déjà-vus!
INSERT INTO my_table (customer_id, domain)
SELECT DISTINCT customer_id, 'sql-paradise.io'
FROM your_table
WHERE type = 'foo';


This will plug in unique customer_id records with a fresh new domain, fence off needless repetition.

Preserving data integrity

You don't want to step on the toes of Primary keys:

-- The Highlander principle: There can be only one!
INSERT INTO my_table (id, customer_id, domain)
SELECT MAX(id)+1, customer_id, 'sql-paradise.io'
FROM your_table
GROUP BY customer_id;


By grouping the data and selecting the max id, we keep those key conflicts at bay!

The nitty gritty

As we peel more layers, let's figure out some curveballs and how to dodge them.

Error hunting

Make sure your SELECT query isn’t going overboard with the scoop. You could run the SELECT statement solo first, and see the data it corrals.

Testing and note-taking

You want to try your fancy moves in a sandbox environment before taking centre stage. Also, jot down your queries, particularly if they are lengthy or have twists and turns.

SQL power plays

Perhaps you're up for some SQL gymnastics, like conditional insertion:

INSERT INTO my_table (id, value)
SELECT id, IF(condition, value_if_true, value_if_false)
FROM your_table;


Or you want to paint with multiple tables as your palette:

INSERT INTO my_table (column1, column2)
SELECT t1.column1, t2.column2
FROM table1 t1
JOIN table2 t2 ON t1.key = t2.key;


Use these advanced techniques as your Swiss army knife—neatly crafting the dataset to suit your every whim.