Insert into ... values (SELECT ... FROM ...)

sql

prompt-engineering

best-practices

performance

byNikita Barsukov·Sep 23, 2024

To INSERT data from one table into another, use this trusty SQL pattern:

INSERT INTO target_table (target_column1, target_column2, ...)
SELECT source_column1, source_column2, ...
FROM source_table
WHERE condition;

This is your ticket to easily transfer data from source_table matching condition to target_table. Ensure the data types align like dancers in a perfect ballet, and no unique keys or constraints are running the stage.

For a quick example where an applicant gets the job:

INSERT INTO employees (name, department)
SELECT applicant_name, applied_department
FROM applicants
WHERE interviewed AND passed;

Sudden existential crisis: what if you need a single-row insertion? No worry - put a single-row SELECT statement on the stage with VALUES:

INSERT INTO products (name, price)
VALUES ((SELECT name FROM upcoming_products WHERE id = 1), (SELECT price FROM upcoming_products WHERE id = 1));

Just ensure your subquery returns exactly one row - it's called a singular existential crisis for a reason!

Working with data and column targeting

Single-row insertions with unique values

Sometimes you like to spice things up a bit by mixing static values with a subquery:

INSERT INTO reports (date, summary)
VALUES (CURRENT_DATE, (SELECT COUNT(*) FROM sales WHERE sales_date = CURRENT_DATE));

Here, CURRENT_DATE is an unchanging constant like a North Star, and the subquery is your journey through today's sales for the summary.

Column targeting and identifier quirks

In case you're a sharpshooter aiming for specific columns, list them after the INSERT INTO clause:

-- Trust me, everyone wants to be a part of the Engineering squad!
INSERT INTO team (id, name, role)
SELECT employee_id, employee_name, 'Squad Member'
FROM staff
WHERE department = 'Engineering';

Remember, each DBMS has its own unique dress code for identifiers - MS SQL Server loves square brackets [ ], Informix is a fan of the classic double quotes ". For Informix users, JFYI, DELIMIDENT is your go-to settings ring to twirl for delimited identifiers.

Schema twister

Table names are usually straightforward, but when referencing, understanding its schema or 'owner' (Informix's term of endearment) becomes important. Careful in ANSI mode; it could be a stickler for casing.

Aggregate functions - party tricks!

Aggregate functions in an INSERT subquery are life of the party, making everyone raise their glasses to data summarization:

-- Let's toast to every month's sales!
INSERT INTO monthly_sales_summary (month, total_sales)
SELECT EXTRACT(MONTH FROM sale_date), SUM(amount)
FROM sales
WHERE EXTRACT(YEAR FROM sale_date) = 2023
GROUP BY EXTRACT(MONTH FROM sale_date);

Inserting multiple constants and rows

Got a lot of things to say in one go? Add multiple constant values as a part of the SELECT:

-- Why generate a series? 'Cause we've got a lot to share!
INSERT INTO notifications (message, date_sent)
SELECT 'Check out our new features!', CURRENT_DATE
FROM generate_series(1, 10);

For multi-row inserts, just let INSERT INTO... waltz with SELECT... without needing the VALUES chaperone:

-- Houston, we're backing up!
INSERT INTO backup_table
SELECT * FROM main_table
WHERE last_updated < CURRENT_TIMESTAMP - INTERVAL '1 month';

Solving hiccups and caveats

Dealing with duplicates and conflicts

Running into mirrors everywhere with those duplicate or conflict errors? Use your DBMS-specific arsenal like ON CONFLICT (PostgreSQL) or ON DUPLICATE KEY UPDATE (MySQL).

Conditional romantic insertions

Like a selective cupid, you may want to insert rows only if they met a certain condition. Use the WHERE clause in the SELECT or sprinkle some CASE statements within the SELECT.

Taking the partition route

Partitioned tables? Make sure you whisper the partition's name in your INSERT INTO clause, if your DBMS hearts it.

Locks and performance

Large data inserts are like a big party - locks and performance need to be managed. Think about fine-tuning transaction isolation levels or organizing small batch parties. SQL Server's BULK INSERT can be a great DJ, especially for large data sets.

explain-codes / Sql / Insert into ... values (SELECT ... FROM ...)

Linked

Converting Select results into Insert script - SQL Server



What is the difference between single and double quotes in SQL?



Insert vs INSERT INTO



Insert Data Into Temp Table with Query



Ora-00979 not a group by expression

