How to do INSERT into a table records extracted from another table

sql

insert-into-select

sql-queries

data-aggregation

byNikita Barsukov·Oct 14, 2024

Work magic with the INSERT INTO SELECT statement to transport records from one table to another with identical columns. It's your SQL broomstick.

-- Avada Kedavra! Selective data transfer spell.
INSERT INTO DeathEater_records (FirstName, LastName)
SELECT FirstName, LastName
FROM Hogwarts_students
WHERE BloodStatus = 'Pure-Blood';

Adjust DeathEater_records as your target, Hogwarts_students as your data origin, and specify your columns. Flavor the WHERE clause to single out specific data.

Keep an eye on matched column names and data types such as LongIntColumn1 and CurrencyColumn2, to dodge rogue Bludgers (errors). Inspect and test your spell - I mean, your query - on stray data.

Comprehensive guide to INSERT INTO SELECT

Show off your aggregation skills

Sometimes, you want to show off and cluster data when transferring. Let's calculate averages:

-- In Gringotts, we trust.
INSERT INTO MoneyManagement (AverageGalleons)
SELECT AVG(Galleons)
FROM GringottsVaults
GROUP BY VaultNumber;

We use AVG() and GROUP BY to spin gold (average Galleons) per vault before landing it in MoneyManagement.

Transform while you port

Change data during the Select operation. Use your wand (aliases) and incantations (functions) in the SELECT clause:

-- Prof. McGonagall sends her regards.
INSERT INTO Minerva_FanClub (MemberName, EnrolmentDate)
SELECT StudentName, TO_CHAR(JoiningDate, 'YYYY-MM-DD')
FROM Hogwarts_database;

See how TO_CHAR() is used to morph a date for Minerva_FanClub?

Common snares and sneak-arounds

Accio type mismatch error

Ensure data types match; you wouldn't expect a quill when you summoned an owl, right?

Curse you, constraints

Watch out for constraints; Prof. Snape wasn't joking about deducting points for duplicated potion ingredients in recipes!

Pesky NULL values

Watch how your source table twirls NULL values, and if your target table can spin along. If needed, use COALESCE for a substitute value:

-- NULL? No, thank you!
INSERT INTO OrderOfPhoenix (wizard, patronus)
SELECT wizard, COALESCE(patronus, 'Stag')
FROM Dumbledore_Army;

explain-codes / Sql / How to do INSERT into a table records extracted from another table

Linked

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



Python and SQLite: insert into table



Converting Select results into Insert script - SQL Server



How can I append a string to an existing field in MySQL?



How to Concatenate Numbers and Strings to Format Numbers in T-SQL?



Insert the same fixed value into multiple rows



How to insert multiple rows in SQLite?



Comprehensive guide to INSERT INTO SELECT Common snares and sneak-arounds