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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+Sample table: agentbangalore
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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:
-- This SQL code attempts to insert rows into the 'agentbangalore' table by selecting data from the 'agents' table based on a condition.
-- INSERT INTO statement begins
INSERT INTO agentbangalore
-- Specifies the target table 'agentbangalore' where the data will be inserted
SELECT * FROM agents
-- Selects all columns from the 'agents' table
WHERE working_area="Bangalore";
-- Filters the rows selected from the 'agents' table based on the condition that the 'working_area' column equals "Bangalore"
Explanation:
- This SQL code aims to insert rows into the 'agentbangalore' table.
- The INSERT INTO statement specifies the target table 'agentbangalore' where the data will be inserted.
- The SELECT statement is used to retrieve data from the 'agents' table.
- The '*' wildcard is used to select all columns from the 'agents' table.
- The WHERE clause filters the rows selected from the 'agents' table based on the condition that the 'working_area' column equals "Bangalore".
- Only the rows meeting this condition will be inserted into the 'agentbangalore' table.
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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+Sample table: agents
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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:
-- This SQL code attempts to insert sorted rows into the 'agentbangalore' table by selecting data from the 'agents' table based on a condition.
-- INSERT INTO statement begins
INSERT INTO agentbangalore
-- Specifies the target table 'agentbangalore' where the data will be inserted
SELECT * FROM agents
-- Selects all columns from the 'agents' table
WHERE working_area="Bangalore"
-- Filters the rows selected from the 'agents' table based on the condition that the 'working_area' column equals "Bangalore"
ORDER BY agent_name DESC;
-- Orders the selected rows in descending order based on the 'agent_name' column
Explanation:
- This SQL code aims to insert sorted rows into the 'agentbangalore' table.
- The INSERT INTO statement specifies the target table 'agentbangalore' where the data will be inserted.
- The SELECT statement is used to retrieve data from the 'agents' table.
- The '*' wildcard is used to select all columns from the 'agents' table.
- The WHERE clause filters the rows selected from the 'agents' table based on the condition that the 'working_area' column equals "Bangalore".
- Only the rows meeting this condition will be inserted into the 'agentbangalore' table.
- The ORDER BY clause orders the selected rows in descending order based on the 'agent_name' column.
- As a result, the sorted rows based on the 'agent_name' column will be inserted into the 'agentbangalore' table.
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
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012
Sample table: daysorder
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012
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:
-- This SQL code attempts to insert aggregated data into the 'daysorder' table by selecting and summarizing data from the 'orders' table.
-- INSERT INTO statement begins
INSERT INTO daysorder (ord_date,ord_amount,advance_amount)
-- Specifies the target table 'daysorder' and the columns where the data will be inserted
SELECT ord_date,SUM(ord_amount),SUM(advance_amount)
-- Selects the 'ord_date' column from the 'orders' table and calculates the sum of 'ord_amount' and 'advance_amount' for each 'ord_date'
FROM orders
-- Specifies the source table 'orders' from which the data will be selected
GROUP BY ord_date;
-- Groups the rows from the 'orders' table by 'ord_date' and calculates the sums for each group
Explanation:
- This SQL code aims to insert aggregated data into the 'daysorder' table.
- The INSERT INTO statement specifies the target table 'daysorder' and the columns where the data will be inserted: 'ord_date', 'ord_amount', and 'advance_amount'.
- The SELECT statement is used to retrieve data from the 'orders' table and perform aggregations.
- The 'ord_date' column is selected directly, and the SUM() function is used to calculate the sum of 'ord_amount' and 'advance_amount' for each 'ord_date'.
- The FROM clause specifies the source table 'orders' from which the data will be selected.
- The GROUP BY clause groups the rows from the 'orders' table by 'ord_date' so that the SUM() function calculates the sums for each group of rows with the same 'ord_date'.
- As a result, the aggregated data is inserted into the 'daysorder' table with one row for each distinct 'ord_date', along with the sum of 'ord_amount' and 'advance_amount' for that 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
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012
Sample table: daysorder
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012
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:
-- This SQL code attempts to insert aggregated and sorted data into the 'daysorder' table by selecting and summarizing data from the 'orders' table.
-- INSERT INTO statement begins
INSERT INTO daysorder (ord_date,ord_amount,advance_amount)
-- Specifies the target table 'daysorder' and the columns where the data will be inserted
SELECT ord_date,SUM(ord_amount),SUM(advance_amount)
-- Selects the 'ord_date' column from the 'orders' table and calculates the sum of 'ord_amount' and 'advance_amount' for each 'ord_date'
FROM orders
-- Specifies the source table 'orders' from which the data will be selected
GROUP BY ord_date
-- Groups the rows from the 'orders' table by 'ord_date' and calculates the sums for each group
ORDER BY ord_date DESC
-- Orders the selected rows in descending order based on the 'ord_date' column
Explanation:
- This SQL code aims to insert aggregated and sorted data into the 'daysorder' table.
- The INSERT INTO statement specifies the target table 'daysorder' and the columns where the data will be inserted: 'ord_date', 'ord_amount', and 'advance_amount'.
- The SELECT statement is used to retrieve data from the 'orders' table and perform aggregations.
- The 'ord_date' column is selected directly, and the SUM() function is used to calculate the sum of 'ord_amount' and 'advance_amount' for each 'ord_date'.
- The FROM clause specifies the source table 'orders' from which the data will be selected.
- The GROUP BY clause groups the rows from the 'orders' table by 'ord_date' so that the SUM() function calculates the sums for each group of rows with the same 'ord_date'.
- The ORDER BY clause orders the selected rows in descending order based on the 'ord_date' column.
- As a result, the aggregated and sorted data is inserted into the 'daysorder' table, with one row for each distinct 'ord_date', along with the sum of 'ord_amount' and 'advance_amount' for that date.
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.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Insert null
Next: Insert using subqueries