SQL INSERT INTO SELECT with Practical Examples


Written by - Falguni Thakker
Reviewed by - Deepak Prasad

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

patient_id name age gender address disease doctor_id
1 reema 23 female althan,Surat fever 21
2 kusum 50 female vadodara heart failure 22
3 carlin 43 male vapi infection 23
4 rahul 26 male navsari cancer 21
6 hansha 55 female vapi diabetes 22

Doctor Table

doctor_id name age gender address
21 asif 55 male baruch
22 dhawal 40 male Surat
23 krishna 39 female Surat
24 lissa 35 female Navsari
25 leeba 34 female baruch
26 vini 33 female Surat
27 Dhiren 32 male Navsari

Bill Table

bill_no patient_id doctor_id room_charge no_of_days
5005 1 340 500 4
5006 2 600 480 8
5008 3 800 340 3
5009 4 780 890 6
5010 3 400 1
5011 1 200 300 1
5012 2 600 110 2
5013 3 330 210 1
5014 1 230 340 2

Laboratory Table

lan_no patient_id doctor_id date amount
10 1 21 02-02-2000 4000
20 2 21 09-09-2001 300
30 3 22 03-03-2001 600
40 1 23 02-06-2002 800
50 4 21 05-07-2003 900
60 2 25 10-04-2004 550
70 4 22 03-04-2005 900

 

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 Practical Examples

 

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

SQL INSERT INTO SELECT with Practical Examples

 

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 with Practical Examples

 

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 with Practical Examples

 

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 with Practical Examples

 

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

SQL INSERT INTO SELECT with Practical Examples

 

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

Views: 2

Falguni Thakker

She is a dedicated professional with expertise in SQL, Python, C++, and Linux. Currently serving as a professor at a prestigious university. With a passion for teaching and a strong technical background, she inspires the next generation of computer scientists. You can connect with her on LinkedIn.

Categories SQL

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment