SQL INSERT INTO SELECT with Practical Examples

Overview of SQL INSERT INTO SELECT statement

SQL insert into select statement is used to insert data from one table to another, where data is the resulting set of records using SQL Select statement, using SQL insert into select statement we are copying the records of one table and inserting it to another so the existing records of targeted and source tables are unaffected, The INSERT INTO SELECT statement requires that the data types in source and target tables match

 

Syntax of SQL INSERT INTO SELECT

INSERT [ TOP ( expression ) [ PERCENT ] ] INTO target_table [(column1, column2, column3, ...])
SELECT column1, column2, column3, ...
FROM source_table
[WHERE condition];

 

Syntax argument of SQL INSERT INTO SELECT

• target_table : It is the name of the table in which the data will be inserted or copied from the source table, the database name and schema name need to specify if the targeted table is of another database
• source_table : It is a name of the table from which data will be derived using SQL select statement
• (column1, column2, column3, ...) : Name of the columns to be fetched from the source table and inserted to the destination table, targeted and source table column name must be matched. column name with targeted_table name is optional, if we do not specify value will insert in all columns
• [WHERE condition] : The where condition clause is optional, it allows us to conditionally retrieve rows from the table and to be inserted into target table
• [ TOP ( expression ) [ PERCENT ] ] : The TOP clause part is optional. It allows you to specify the number of rows returned by the query to be inserted into the target table. If you use the PERCENT option, the statement will insert the percent of rows instead

 

Key points to remember for SQL INSERT INTO SELECT

• The Column structure should match between the column returned by SELECT statement and destination table
• SQL insert into select statement can also be used to efficiently transfer a large number of rows from one table, such as a staging table, to another table with minimal logging
• It is a best practice to always use the TOPclause with the ORDER BY clause

 

Examples of SQL INSERT INTO SELECT

Consider hospital management database for performing practical examples of SQL insert into select Statement
Patient Table

Doctor Table

Bill Table

Laboratory Table

 

SQL INSERT INTO SELECT to insert all records of one table to another table

Example 1: Write SQL query to insert all records of patient table to admitted_patient table

INSERT INTO admitted_patient
SELECT patient_id, name, age, gender, address, city,disease, doctor_id
FROM patient

• In the above query, SQL insert into select statement is used to insert records of the patient table into admitted_patient table
• name of columns do not specify with admitted_patient table so the data will be copied in the matched column of the patient table

OUTPUT:

To see the output of the above query we need to use the SQL Select statement to fetch records of a targeted table

SELECT patient_id, name, age, gender, address, city, disease, doctor_id
FROM dbo.admitted_patient

 

SQL INSERT INTO SELECT with Where condition

Using the where clause we can conditionally retrieve records from the source table and insert them into targeted table

Example 2: Write SQL query to fetch female patient data from the patient table and insert that records to female_patient table

INSERT INTO female_patient_data (patient_id, name, age, gender, address,city, disease, doctor_id)
SELECT patient_id, name, age, gender, address, city,disease, doctor_id
FROM  patient
WHERE (gender = 'female')

• In the above query, SQL insert into a select statement with where the condition is used to retrieve female patient data from the patient table and insert into the female_patient_data table
• In this query where the condition is used to check for gender-equal to female value in the patient table
• when we execute the above query 3 records from the patient table will be fetched and inserted into the female_patient_data table

OUTPUT:
To see the output of the above query we need to use the SQL Select statement to fetch records of a targeted table

SELECT patient_id, name, age, gender, address, city, disease, doctor_id
FROM  dbo.female_patient_data

 

Example 3: Write SQL query to copy patient records whose age is more than 60 into the oldage_patient table

INSERT INTO oldage_patient
SELECT        patient_id, name, age, gender, address, city, disease, doctor_id
FROM            patient
WHERE        (age > 60)

• In the above query, SQL insert into a select statement with where the condition is used to retrieve old age patient data from the patient table and insert into oldage_patient table
• In this query where the condition is used to check for patient age, the value must be more than 60 value
• when we execute the above query 1 records from the patient table will be fetched and inserted into oldage_patient table

OUTPUT:

To see the output of the above query we need to use the SQL Select statement to fetch records of a targeted table

SELECT   patient_id, name, age, gender, address, city, disease, doctor_id
FROM   oldage_patient

 

SQL INSERT INTO SELECT to insert the top N of rows with the order by

Example 4: Write SQL query to insert top 5 laboratory reports in the increment order of amount into min_amount_lab_report table

INSERT INTO min_amount_lab_report
SELECT TOP (3) lab_no, patient_id, doctor_id, date, amount
FROM laboratory
ORDER BY amount

• In the above query, SQL insert into select statement is used with a top clause to insert only top 3 records from the selected recordset using select statement with an order by clause
• The first inner SQL select statement will be executed and it will return all records of the laboratory table in the increment order of the amount
• The outer insert into the statement will insert the top 3 records from the selected recordset into the min_amount_lab_report table

OUTPUT:

To see the output of the above query we need to use the SQL Select statement to fetch records of the targeted table

SELECT  lab_no, patient_id, doctor_id, date, amount
FROM  min_amount_lab_report

 

SQL INSERT INTO SELECT to insert the top percent of rows with an order by desc

Example 5: Write SQL query to retrieve patient billing data in the decrement order of room charges and insert top 10% record into max_room_charge table

INSERT INTO max_room_charge
SELECT TOP (10) PERCENT bill_no, patient_id, doctor_id, doctor_charge, room_charge, no_of_days
FROM  bill
ORDER BY room_charge DESC

• In the above query, SQL insert into select statement is used to insert top 10% maximum room_charges of bill table into max_room_charge table
• The first inner SQL Select statement is executed and it will return bill table records in the descending order of room charges
• Outer insert into top(10) percent will insert top 10% records of total records into max_room_charge table

OUTPUT:

To see the output of the above query we need to use the SQL Select statement to fetch records of a targeted table

SELECT bill_no, patient_id, doctor_id, doctor_charge, room_charge, no_of_days
FROM max_room_charge

 

SQL INSERT INTO SELECT statement with Join clause to get data from multiple tables

We can use a JOIN clause to get data from multiple tables, These tables are joined with conditions specified with the ON clause

Example 6: Write SQL query to insert patient data with billing detail into oldage_patient_bill whose age is more than 40 and admitted for more than 2 days in the hospital

Insert into oldage_patient_bill select patient.patient_id,name,age,disease,bill_no,bill.doctor_id,no_of_days 
from patient 
left join bill on patient.patient_id=bill.patient_id 
where age >40 and no_of_days>2

• In the above query, SQL insert into select statement is used with left outer join to retrieve data from two tables patient and bill
• The first inner select statement is executed and return join records set of bill and patient table in which age value is more than 40 and no of admitted days are more than 2
• Outer insert into statement will insert resulted record set of select statement into oldage_patient_bill table

OUTPUT:
To see the output of the above query we need to use the SQL Select statement to fetch records of a targeted table

SELECT   patient_id, name, age, disease, bill_no, doctor_id, no_of_days
FROM dbo.oldage_patient_bill

 

Summary

This article of SQL INSERT INTO SELECT, the article begins with the overview of SQL INSERT INTO SELECT with syntax, use of each syntax argument and clause, the key point to remember for SQL INSERT INTO SELECT, practical examples are also discussed starts with a simple example to insert all records of one table to another, conditionally insert data of one table to another using where clause, insert the top N of rows with an order by, insert the top percent of rows with an order by desc, Insert with Join clause to get data from multiple tables

 

References

SQL INSERT