Copying and Transforming Data with INSERT SELECT

The Power of INSERT INTO ... SELECT

So far, we've used INSERT with a VALUES clause to add a single, new row of data. But what if the data you want to insert already exists in another table? The INSERT INTO ... SELECT statement is the perfect tool for this. It lets you use the full power of a SELECT query—including WHERE clauses, functions, and JOINs—to define a set of rows that you then insert into another table in a single command.

Let's start with a short video that demonstrates the basic syntax.

The fundamental syntax is:

INSERT INTO target_table (column1, column2, ...)
SELECT expression1, expression2, ...
FROM source_table
WHERE condition;

As the video highlighted, there are two golden rules for this to work:

1. Column Count: The number of columns in your SELECT list must match the number of columns in your INSERT list.
2. Data Type Compatibility: The data type of each column in the SELECT list must be compatible with the corresponding column in the target_table. You can't insert a text string like 'hello' into an INTEGER column.

Practical Scenarios: Archiving, Copying, and Transforming

This command is incredibly versatile. You can perform a simple one-to-one copy, or you can get creative and modify the data as you move it. Let's explore some common use cases.

The following article provides excellent examples of how to handle different scenarios, from a simple full-table copy to more complex situations where columns don't perfectly align.

Transforming Data On-the-Fly

The "transform" part of this technique is where it truly shines. As you saw in Example 4 of the article, you're not limited to just copying existing data. The SELECT statement can include anything that produces a value, such as:

• Static values: 'Active', 100, CURRENT_DATE
• Calculations: price * 1.1 to add a 10% markup
• String manipulation: first_name || ' ' || last_name to create a full name (syntax for concatenation varies between SQL dialects, e.g., + in SQL Server).

Here is a great visual example of creating a new table and then populating it with filtered and transformed data from another table.

INSERT INTO on_sale_items (item_name, sale_price)
SELECT name, price * 0.9
FROM products
WHERE price > 100;

A Critical Distinction: INSERT INTO vs. SELECT INTO

In your journey to understand the "why" behind database operations, it's vital to know about a similar-looking but functionally different command: SELECT INTO. This is a common point of confusion.

• INSERT INTO ... SELECT ...: Inserts data into an existing table.
• SELECT ... INTO ...: Creates a new table and populates it with data.

Let's watch a video that clearly demonstrates both commands, so you can see the difference in action.

To deepen this understanding, let's look at how professional developers discuss this topic. This connects directly to your goal of being job-ready.

In summary:

• Use INSERT INTO ... SELECT for routine DML operations on your permanent, well-designed tables.
• Use SELECT INTO primarily for creating temporary tables for a specific procedure or for making quick, one-off backups. It's more of a DDL (Data Definition Language) operation as it creates a database object.

Advanced Application: Consolidating Data with JOINs

The real power of INSERT INTO ... SELECT is that the SELECT statement can be as complex as you need. You can use JOINs to gather data from multiple tables and consolidate it into a single summary or archive table.

For instance, you could join customers, orders, and products tables to create a new quarterly_sales_summary table.

The SQLShack article we looked at earlier has a great example of this.

Conclusion

You've now learned one of the most practical and powerful commands in SQL for bulk data management. This completes our module on the essentials of Data Manipulation Language.

Key Takeaways:

• The INSERT INTO ... SELECT statement copies rows from a source and inserts them into a target table.
• The source data can be filtered with a WHERE clause and transformed using functions and expressions.
• It is crucial that the number of columns and their data types are compatible between the SELECT and INSERT clauses. Explicitly naming columns is a best practice.
• INSERT INTO ... SELECT works on an existing table (a DML operation).
• SELECT ... INTO creates a new table (a DDL-like operation) and is typically used for temporary tables.

Preview of the Next Lesson:
We've spent a lot of time working with data inside tables. But how do we decide what tables we need and what columns should go in them? In the next module, we will shift our focus to Database Design and Normalization. You'll learn the principles behind designing a clean, efficient, and robust database schema, starting with how to identify entities, attributes, and relationships—a topic that strongly appeals to your preference for logical, foundational learning.