How Do You Use “INSERT INTO (SELECT & VALUES)” Together?

Using INSERT INTO with both SELECT and VALUES in the same statement isn’t directly possible in most relational database systems because these two clauses serve different purposes. However, there are ways to achieve similar results depending on your use case.

Here’s how you can approach this:

Scenario 1: Use SELECT for Dynamic Data Insertion

If you want to insert data based on a SELECT query:

INSERT INTO target_table (column1, column2, column3)
SELECT columnA, columnB, columnC
FROM source_table
WHERE condition;

This is ideal when the data you want to insert comes from another table.

Scenario 2: Use VALUES for Static Data Insertion

If you want to insert hard-coded values:

INSERT INTO target_table (column1, column2, column3)
VALUES (value1, value2, value3);

This works when you know the exact values to insert.

Combining SELECT and VALUES Workaround

If you need to mix hard-coded values and values from a SELECT query, you can use a UNION ALL to combine both data sources and then insert them into the target table.

Example:

INSERT INTO target_table (column1, column2, column3)
SELECT 'static_value1', 'static_value2', 'static_value3'
UNION ALL
SELECT columnA, columnB, columnC
FROM source_table
WHERE condition;

In this example:

• The first SELECT adds static values.
• The second SELECT pulls data dynamically from source_table.
• UNION ALL combines the results, which are then inserted into target_table.

Example with Multiple Static and Dynamic Rows

Let’s say you want to insert a mix of static data and rows from another table into target_table:

INSERT INTO target_table (column1, column2, column3)
SELECT 'Static1', 'Static2', 'Static3'
UNION ALL
SELECT columnA, columnB, columnC
FROM source_table
WHERE columnA = 'Condition';

This approach is versatile and can handle both use cases effectively.

Key Points

• Use VALUES for inserting static data.
• Use SELECT for inserting data dynamically from other tables.
• Combine both using UNION ALL for mixed data sources.

This method works across most relational databases like MySQL, PostgreSQL, and SQL Server. However, always test your queries in your specific database environment.