Part 6: Mastering INSERT INTO SELECT

Part 6: Mastering INSERT INTO SELECT | SQL Challenges & Capstone
SQL Set Operations & Advanced Filtering Practice · Part 6 · Lecture Notes

Mastering INSERT INTO SELECT

How to copy records from one table into another using SQL — archiving filtered business data, populating reporting tables, and automating data movement.

Part 6 of 6100% Complete
INSERT INTO SELECT
Create Table First
WHERE Filter
Data Movement
8 Practice Questions

Learning Objectives

  • Use INSERT INTO SELECT to copy records from one table into another using a SELECT query
  • Create the destination table with the correct structure before running INSERT INTO SELECT
  • Add a WHERE clause to the SELECT to copy only filtered subsets of records
  • Understand that INSERT INTO SELECT copies data — the source table remains unchanged
Click any section header to open or close it
Understanding “No Rows Were Returned”

Students often assume that the message “Query ran successfully. No rows were returned” means something went wrong. In fact, this usually means the database completed the requested action successfully.

When using INSERT INTO … SELECT, the goal is to copy data, not display it. The database performs the insert operation, but it does not automatically show the inserted records.

Key Point: If an INSERT INTO … SELECT statement returns “Query ran successfully. No rows were returned,” the operation likely worked correctly. The message indicates that data was copied, not that data was displayed. Students should run a separate SELECT query if they want to view the inserted records.

INSERT INTO SELECT copies records from one table into another table using a SELECT query to define which records to copy. The destination table must already exist before running INSERT INTO SELECT. The SELECT can include a WHERE clause to copy only filtered subsets, or no WHERE clause to copy all records.

In industry, analysts use INSERT INTO SELECT to move operational data between tables, create backup records before making changes, replicate filtered business information into reporting tables, archive completed or historical records, and transfer data between systems as part of ETL (Extract, Transform, Load) workflows.

Three Requirements for INSERT INTO SELECT

The destination table must already exist before the INSERT runs. The number of columns in the INSERT INTO clause must match the number of columns in the SELECT. The data types must be compatible between source and destination columns.

Basic INSERT INTO SELECT Syntax
INSERT INTO destination_table (column1, column2) SELECT column1, column2 FROM source_table;

Each INSERT INTO SELECT practice follows a two-step pattern: first create the destination table with the correct structure, then run the INSERT INTO SELECT to populate it. In production environments, destination tables are typically created once and reused; in practice exercises, DROP TABLE IF EXISTS ensures a clean start before each run.

The Two-Step Pattern

Step 1: CREATE TABLE target_table (columns and types). Step 2: INSERT INTO target_table (columns) SELECT columns FROM source_table. The CREATE defines the structure; the INSERT populates it. Always confirm column names and data types match before running.

Step 1: Create the Destination Table
CREATE TABLE archived_users ( user_id INT, first_name VARCHAR(50) );
Step 2: Copy All Users Into It
INSERT INTO archived_users (user_id, first_name) SELECT user_id, first_name FROM users;

Adding a WHERE clause to the SELECT inside INSERT INTO SELECT copies only the matching records. Completed orders only. Active subscriptions only. Open tickets only. Email campaigns only. This filtered copying is the most common INSERT INTO SELECT pattern in business analytics — populating an archive or reporting table with only the records that meet a specific business criterion.

WHERE Filters What Gets Copied

INSERT INTO completed_orders_archive … SELECT … FROM orders WHERE order_status = ‘Completed’ copies only the 6 completed orders. The other orders are not copied. WHERE applies to the SELECT, not to the INSERT.

Copy Only Completed Orders
CREATE TABLE completed_orders_archive ( order_id INT, order_status VARCHAR(50) ); INSERT INTO completed_orders_archive (order_id, order_status) SELECT order_id, order_status FROM orders WHERE order_status = ‘Completed’;

INSERT INTO SELECT is the foundation of database-level reporting workflows. Active subscriptions copied to a reporting table. Refund transactions isolated in a separate table. Software products extracted to a product-type archive. These patterns replace manual data exports with automated, reproducible SQL operations that run on a schedule or on demand.

DROP TABLE IF EXISTS Before Practice Runs

When running INSERT INTO SELECT practice queries more than once, the destination table may already exist from a previous run. DROP TABLE IF EXISTS table_name before CREATE TABLE removes the old table cleanly so the exercise can be repeated without errors.

Copy Active Subscriptions to Reporting Table
CREATE TABLE active_subscriptions ( subscription_id INT, monthly_fee DECIMAL(10,2) ); INSERT INTO active_subscriptions (subscription_id, monthly_fee) SELECT subscription_id, monthly_fee FROM subscriptions WHERE status = ‘Active’;

Each practice has two steps: create the destination table, then run the INSERT INTO SELECT. Use DROP TABLE IF EXISTS at the start of each exercise to ensure a clean run.

Practice 1 Copy all users into archived_users
AI Prompt
Write MySQL queries to create a table called archived_users and copy all users into it.
Step 1 — Create the Table
DROP TABLE IF EXISTS archived_users; CREATE TABLE archived_users ( user_id INT, first_name VARCHAR(50) );
Step 2 — Insert the Records
INSERT INTO archived_users (user_id, first_name) SELECT user_id, first_name FROM users;
Practice 2 Copy completed orders into completed_orders_archive
AI Prompt
Write MySQL queries to create a table called completed_orders_archive and copy completed orders into it.
Step 1 — Create the Table
DROP TABLE IF EXISTS completed_orders_archive; CREATE TABLE completed_orders_archive ( order_id INT, order_status VARCHAR(50) );
Step 2 — Insert the Records
INSERT INTO completed_orders_archive (order_id, order_status) SELECT order_id, order_status FROM orders WHERE order_status = ‘Completed’;
Practice 3 Copy active subscriptions into active_subscriptions
AI Prompt
Write MySQL queries to create a table called active_subscriptions and copy active subscriptions into it.
Step 1 — Create the Table
DROP TABLE IF EXISTS active_subscriptions; CREATE TABLE active_subscriptions ( subscription_id INT, monthly_fee DECIMAL(10,2) );
Step 2 — Insert the Records
INSERT INTO active_subscriptions (subscription_id, monthly_fee) SELECT subscription_id, monthly_fee FROM subscriptions WHERE status = ‘Active’;
Practice 4 Copy refund transactions into refund_transactions
AI Prompt
Write MySQL queries to create a table called refund_transactions and copy refund transactions into it.
Step 1 — Create the Table
DROP TABLE IF EXISTS refund_transactions; CREATE TABLE refund_transactions ( transaction_id INT, amount DECIMAL(10,2) );
Step 2 — Insert the Records
INSERT INTO refund_transactions (transaction_id, amount) SELECT transaction_id, amount FROM transactions WHERE transaction_type = ‘Refund’;
Practice 5 Copy open support tickets into open_ticket_archive
AI Prompt
Write MySQL queries to create a table called open_ticket_archive and copy open support tickets into it.
Step 1 — Create the Table
DROP TABLE IF EXISTS open_ticket_archive; CREATE TABLE open_ticket_archive ( ticket_id INT, ticket_status VARCHAR(50) );
Step 2 — Insert the Records
INSERT INTO open_ticket_archive (ticket_id, ticket_status) SELECT ticket_id, ticket_status FROM support_tickets WHERE ticket_status = ‘Open’;
Practice 6 Copy Email campaigns into email_campaign_archive
AI Prompt
Write MySQL queries to create a table called email_campaign_archive and copy Email campaigns into it.
Step 1 — Create the Table
DROP TABLE IF EXISTS email_campaign_archive; CREATE TABLE email_campaign_archive ( campaign_id INT, campaign_name VARCHAR(100) );
Step 2 — Insert the Records
INSERT INTO email_campaign_archive (campaign_id, campaign_name) SELECT campaign_id, campaign_name FROM campaigns WHERE channel = ‘Email’;
Practice 7 Copy Desktop sessions into desktop_sessions_archive
AI Prompt
Write MySQL queries to create a table called desktop_sessions_archive and copy Desktop sessions into it.
Step 1 — Create the Table
DROP TABLE IF EXISTS desktop_sessions_archive; CREATE TABLE desktop_sessions_archive ( session_id INT, device_type VARCHAR(50) );
Step 2 — Insert the Records
INSERT INTO desktop_sessions_archive (session_id, device_type) SELECT session_id, device_type FROM sessions WHERE device_type = ‘Desktop’;
Practice 8 Copy Software products into software_products_archive
AI Prompt
Write MySQL queries to create a table called software_products_archive and copy Software products into it.
Step 1 — Create the Table
DROP TABLE IF EXISTS software_products_archive; CREATE TABLE software_products_archive ( product_id INT, product_name VARCHAR(100), unit_price DECIMAL(10,2) );
Step 2 — Insert the Records
INSERT INTO software_products_archive (product_id, product_name, unit_price) SELECT product_id, product_name, unit_price FROM products WHERE category = ‘Software’;

Knowledge Check

Five questions — select the best answer for each.

1. What must be true about the destination table before running INSERT INTO SELECT?
AIt must be empty before the INSERT runs
BIt must already exist with a compatible structure before the INSERT runs
CIt is automatically created by INSERT INTO SELECT
DIt must have the same name as the source table
2. How does a WHERE clause affect INSERT INTO SELECT?
AWHERE filters the destination table after the insert
BWHERE is not allowed inside INSERT INTO SELECT
CWHERE filters the source table rows — only matching rows are copied
DWHERE deletes non-matching rows from the destination table
3. What does DROP TABLE IF EXISTS before CREATE TABLE ensure in practice exercises?
AIt permanently deletes the source data before copying
BIt removes the old destination table so the exercise can be repeated cleanly
CIt speeds up the subsequent INSERT INTO SELECT
DIt is required for INSERT INTO SELECT to work correctly
4. INSERT INTO SELECT copies data from the source table. What happens to the original source data?
AIt is deleted from the source table after copying
BIt is moved to the destination table — the source row is removed
CIt remains unchanged in the source table
DIt is marked as archived in the source table
5. Which query correctly copies active subscriptions into a pre-created active_subscriptions table?
ACOPY active_subscriptions FROM subscriptions WHERE status = ‘Active’;
BINSERT active_subscriptions SELECT subscription_id, monthly_fee FROM subscriptions WHERE status = ‘Active’;
CINSERT INTO active_subscriptions (subscription_id, monthly_fee) SELECT subscription_id, monthly_fee FROM subscriptions WHERE status = ‘Active’;
DSELECT subscription_id, monthly_fee INTO active_subscriptions FROM subscriptions WHERE status = ‘Active’;

Part 6 Summary

The key ideas from this lecture — keep these in mind as you move forward.

INSERT INTO SELECT helps analysts copy, archive, and replicate business records between tables as part of operational workflows and ETL processes. Create the destination table first. WHERE filters which rows are copied. Source data is never changed.

INSERT INTO SELECTCopy RecordsCREATE TABLE FirstWHERE FilterDROP TABLE IF EXISTSArchiveETLData MovementSource Unchanged
Previous Topic
Back to Lesson