Inserting the result of a query in another table

All the rows or some rows of another table can also be inserted into the table using INSERT INTO statement. The rows of another table will be fetched based on one or more criteria using SQL SELECT statement.

Example:

Sample table: agents

Sample table: agentbangalore

To add records of 'agents' table into 'agentbangalore' table with the following condition -

1. the 'working_area' of 'agents' table must be 'Bangalore',

the following SQL statement can be used:

SQL Code:

INSERT INTO agentbangalore
SELECT * FROM agents
WHERE  working_area="Bangalore";

Inserting the result of a query in another table with order by

An arranged order of rows (ascending or descending) of one table can also be inserted into another table by the use of SQL SELECT statement along with ORDER BY clause.

Example:

Sample table: agentbangalore

Sample table: agents

To add records of 'agents' table into 'agentbangalore' table with following conditions -

1. the rows of 'agents' table should be arranged in descending order on 'agent_name' column,

2. the 'working_area' of 'agents' table must be 'Bangalore',

the following SQL statement can be used:

SQL Code:

INSERT INTO agentbangalore
SELECT * FROM agents
WHERE  working_area="Bangalore"
ORDER  BY agent_name DESC;

Inserting the result of a query in another table with group by

A group of rows of one table can also be inserted into another table by the use of SQL SELECT statement along with GROUP BY clause.

Example:

Sample table: orders

Sample table: daysorder

To add records into 'daysorder' table for the columns 'ord_date','ord_amount' and 'advance_amount' from the same columns of 'orders' table with following conditions -

1. the rows of 'orders' table should arranged into a group according to 'ord_date',

2. make a sum of 'ord_amount' for each group,

3. make a sum of 'advance_amount' for each group,

4. data of each group in 'orders' table should be inserted into the 'daysorder' table,

the following SQL statement can be used:

SQL Code:

INSERT INTO daysorder (ord_date,ord_amount,advance_amount)
SELECT ord_date,SUM(ord_amount),SUM(advance_amount)
FROM orders
GROUP BY ord_date;

Inserting records using select with group by and order by

In the following we are going to discuss, how records of another table can be inserted using SQL SELECT statement along with ORDER BY and GROUP BY in an INSERT INTO statement.

Example:

Sample table: orders

Sample table: daysorder

To insert records into 'daysorder' table for the columns 'ord_date','ord_amount' and 'advance_amount' from the same columns of 'orders' table with following conditions -

1. the rows of 'orders' table should be arranged into a group according to 'ord_date',

2. the rows of 'orders' table should be arranged in descending order on 'ord_date' column,

3. make a sum of 'ord_amount' for each group ,

4. make a sum of 'advance_amount' for each group ,

5. data of each group in 'orders' table should insert into the 'daysorder' table,

the following SQL statement can be used :

SQL Code:

INSERT INTO daysorder (ord_date,ord_amount,advance_amount)
SELECT ord_date,SUM(ord_amount),SUM(advance_amount)
FROM orders 
GROUP BY ord_date
ORDER BY ord_date DESC

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: Insert null
Next: Insert using subqueries