Using the SELECT Statement to INSERT Rows from One Table into Another

Although used primarily to add one row of data to a table, you can use a single INSERT statement to add multiple rows to table at once. In fact, you can use an INSERT statement to copy all or part of one table into another.

Using the INSERT Statement to Add Data to Specific Columns in a Row," the syntax of the basic INSERT statement is:


[( [...,])]
VALUES ([...,])

Well, the VALUES clause of the INSERT statement should actually be written as:


[( [...,])]

Here, each is a list of column values that matches the number (and type) of columns in the statement's column name list (which follows the table name).

you can add additional row values to the same statement when you want to add multiple rows to the table at once. For example, the statement

INSERT INTO employee VALUES (1,'Konrad','King',
'555-55-5555','7810 Greenwood Ave',NULL,NULL)

with one row value constructor (introduced by the keyword VALUES) will add one row to the employee table, while the statement

INSERT INTO employee
(employee_id, first_name, last_name,
social_security_number, street_address,
health_card_number, sheriff_card_number)
VALUES (1, 'Konrad', 'King', '555-55-5555',
'7810 Greenwood Ave',NULL,NULL),
(2, 'Sally', 'Fields', '556-55-5555',
'77 Sunset Strip',NULL,NULL),
(3, 'Wally', 'Wallberg', '557-55-5555',
'765 E. Eldorado Lane',NULL,NULL)

with three row value constructors (following the keyword VALUES) will add three rows to the same table.

Note As you learned previously, the column list (following the table name) in the two example INSERT statements in the previous paragraph are equivalent. When you do not specify column names (as is the case in the first INSERT statement), the DBMS supplies the INSERT statement with a column name list that consists of all columns in the table (as is explicitly enumerated in the second INSERT statement).

Thus far, the example INSERT statements in this tip list values for all columns in a table row. However, as is the case with a single-row INSERT statement, multi-row INSERT statements can supply values for all or part of a row's columns. For example, the statement

INSERT INTO employee (employee_id, first_name, last_name)
VALUES (4, 'Joe', 'Kernan'), (5, 'David', 'Faber'),
(3, 'Brad', 'Woodyard')

will add three rows to the EMPLOYEE table. (The DBMS will assign the column default value [NULL, in the current example] to each of the columns not list in the column name list.)

Because SQL lets you use a single INSERT statement to add multiple rows to a table at once, you can replace the VALUES clause with a SELECT statement—as long as the rows constructed by the SELECT statement have the same number and type of columns as those found in the column name list (which follows the table name in the INSERT statement).

Thus, to INSERT rows from one table into another, use the INSERT statement syntax:


[( [...,])]
SELECT [...,]


As such, to insert all employees in the EMPLOYEE table into a second employees table, EMPLOYEE2, you could use the INSERT statement:

INSERT INTO employee2 SELECT * from employee

Note In order for the SELECT * clause in the example INSERT statement to work, both EMPLOYEE and EMPLOYEE2 must have the same number of columns in the same order. In the following paragraphs, you will learn how you can get around this restriction by listing column names explicitly vs. using the column name list implicit in the SELECT * clause. The important thing to know now is that you can copy data from one table into another either by listing the specific columns whose data you want copied or by omitting the column list, in which case the DBMS assumes that you want to copy all of the column values from one table into another.

If you have several queries to run against data from multiple tables, you will find that your queries are simpler and execute more quickly if you first consolidate the data from multiple tables into one temporary table and then execute your SQL statements as single table queries against the new aggregate table. Accumulating data into a single data avoids having the DBMS search multiple tables multiple times, repeatedly reading and eliminating the same data that does not meet your search criteria.

For example, given CUSTOMERS, EMPLOYEES, and ORDERS tables defined as follows

CREATE TABLE customers
(customer_id INTEGER, first_name VARCHAR(30),
last _name VARCHAR(30), address VARCHAR(35),
city VARCHAR (20), state CHAR(2),
zip_code INTEGER, phone_number CHAR(12),
salesperson INTEGER net_due_days SMALLINT,
credit_limit NUMERIC)
CREATE TABLE employees
(employee_id INTEGER, first_name VARCHAR(30),
last_name VARCHAR(30), address VARCHAR(35),
ssan CHAR(11), salary NUMERIC,
low_quota SMALLINT, medium_quota SMALLINT,
high_quota SMALLINT, sales_commission NUMERIC)
(order_id INTEGER, order_date DATETIME,
item_number INTEGER, quantity SMALLINT,
customer_id INTEGER, salesman_id INTEGER)
(product_id SMALLINT, description VARCHAR(40),
quantity_on_hand SMALLINT, item_cost NUMERIC)

you can combine data into a single table defined by

CREATE TABLE temp_report_table
(customer_ID INTEGER,
cust_first_name VARCHAR(30),
cust_last_name VARCHAR(30),
salesman_ID INTEGER,
salesman_first_name VARCHAR(30),
salesman_last_name VARCHAR(30),
order_date DATETIME,
order_item_number SMALLINT,
order_item_quantity SMALLINT,
order_total NUMERIC,
order_item_desc VARCHAR(40))

by using the INSERT statement:

INSERT INTO temp_report_table
(customer_id, cust_first_name, cust_last_name,
salesman_id, salesman_first_name, salesman_last_name,
order_date, order_item_number, order_item_quantity,
order_total, order_item_desc)
customers.customer_id, customer.first_name,
customers.last_name, employee_id, employees.first_name,
employees.last_name, order_date, item_number, quantity,
(quantity * item_cost), description
FROM orders, customers, products, employees
WHERE orders.customer_id = customers.customer_id
AND product_id = item_number
AND employee_id = salesman_id

Once you've combined the data from the four tables into one, you can use a single-table SELECT statement to display data from one or more of the tables. For example, the SELECT statement

cust_first_name, cust_last_name, salesman_first_name,
salesman_last_name, order_item_quantity, order_total,
FROM temp_report_table WHERE order_item_number = 5

will display the names of the salesmen that sold item 5; the names of the customers that purchased it; and the date, quantity, total, and item description for orders for item 5.

You can also use the simple single-table query

SELECT salesman_id, salesman_first_name,
salesman_last_name, order_total
FROM temp_report_table
WHERE order_total =
(SELECT MAX(order_total) FROM temp_report_table)

to display the amount of the largest order and the name of the salesman who sold it.


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: