MySQL INSERT INTO SELECT

How it works

INSERT SELECT writes the rows produced by a query. The target columns and source expressions must align by position and compatible type. This lab creates a small review queue for zero-stock parts. It uses a separate table instead of pretending that copied rows remain synchronized automatically. The queue captures the IDs and names at this moment; subsequent changes to parts do not rewrite that snapshot.

Run in a disposable MySQL lab

Use Oracle MySQL 8.4 with InnoDB, not Bhylu’s SQLite playground. The examples were reviewed on MySQL 8.4.8. XAMPP may provide MariaDB, which has different feature and collation behaviour. Check your actual server before running the script. These lessons do not require a change to Bhylu’s PHP, Apache or normal database service.

1. On a separate lab instance, create the empty bhylu_mysql_lab schema. Stop if it already exists; do not overwrite an existing database.
2. Select that schema and import the complete repair-parts seed once. It sets the documented session mode, utf8mb4 collation, UTC and English date-name locale.
3. Run this lesson in a fresh copy of the seed, not after another lesson’s writes. Use the command-line client or a compatible editor. DELIMITER lines are client instructions for stored-program examples.

Download this complete lesson script. Its setup is the linked seed; review the dataset before changing it. Administrative and destructive examples are restricted to the specifically named disposable objects. Every script repeats the session settings because each new connection has its own session. No MySQL browser runner is currently available.

Example

SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
SET SESSION time_zone = '+00:00';
SET SESSION lc_time_names = 'en_US';
CREATE TABLE review_queue (part_id INT PRIMARY KEY, label VARCHAR(60) NOT NULL) ENGINE=InnoDB;
INSERT INTO review_queue (part_id,label)
SELECT id,name FROM parts WHERE quantity = 0;
SELECT part_id,label FROM review_queue ORDER BY part_id;

Expected result

3 | Cotton cord

Only the cord enters the queue. A primary key prevents the same part from being enqueued twice by this simple design. A retry policy must decide whether a duplicate means success, stale work or a real error. For a production batch, review transaction size, contention and how readers identify a complete batch rather than seeing an accidental partial process.