SQL INSERT INTO SELECT: Copy the Intended Query Result

The practice task

Copy completed fictional lessons from an intake table into a prepared catalogue. Keep a draft lesson in the source to verify that the copy rule excludes it.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE intake(id INTEGER, title TEXT, status TEXT);
CREATE TABLE catalogue(lesson_id INTEGER PRIMARY KEY, title TEXT);
INSERT INTO intake VALUES (1,'Joins','completed'),(2,'Windows','draft'),(3,'Filters','completed');
INSERT INTO catalogue(lesson_id,title)
SELECT id,title FROM intake WHERE status = 'completed';

SELECT lesson_id, title FROM catalogue ORDER BY lesson_id;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The source SELECT produces lessons 1 and 3, and its column order maps id to lesson_id and title to title. The draft Windows lesson remains only in intake. The source rows are not removed by this copy operation.

The target primary key is a separate protection against repeated lesson IDs. Rerunning a plain copy can conflict with existing target rows; it is not automatically an idempotent import. Decide whether to reject, skip or update a repeated key according to the actual workflow.

A query that previews the right rows does not by itself establish that every target constraint will accept them. Check required columns, type rules and identity relationships before adapting the copy. For a larger process, make the intended transaction and repeat-run policy part of the design.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Map target and source columns in the intended order.
• Verify excluded source records as well as copied records.
• Define what a repeated run should do.