INSERT INTO SELECT

SQL INSERT INTO SELECT Keyword

The INSERT INTO SELECT statement copies data from one table and inserts it into another. This is useful when transferring data between tables with similar structures.

SQL INSERT INTO SELECT Syntax

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

INSERT INTO target_table (column1, column2, ...)

SELECT column1, column2, ...

FROM source_table

WHERE condition;

SQL INSERT INTO SELECT Copy Names from Students to Doctors Example

This query copies names from the Students table into the Doctors table, filling only the first and last name columns. Other fields will be NULL.

INSERT INTO Doctors (first_name, last_name)
SELECT name, name
FROM Students
WHERE id < 5;

INSERT INTO Doctors (first_name, last_name)

SELECT name, name

FROM Students

WHERE id < 5;

SQL INSERT INTO SELECT Copy Doctors into Patients Table Example

This query inserts rows from the Doctors table into the Patients table, providing fixed values for columns that require non-null data (like dob and gender):

INSERT INTO Patients (first_name, last_name, dob, gender)
SELECT first_name, last_name, '1990-01-01', 'F'
FROM Doctors
WHERE specialty IS NOT NULL;

INSERT INTO Patients (first_name, last_name, dob, gender)

SELECT first_name, last_name, '1990-01-01', 'F'

FROM Doctors

WHERE specialty IS NOT NULL;

SQL INSERT INTO SELECT Labs