Recipe4.4.Copying Rows from One Table into Another

Recipe 4.4. Copying Rows from One Table into Another

Problem

You want to copy rows from one table to another by using a query. The query may be complex or simple, but ultimately you want the result to be inserted into another table. For example, you want to copy rows from the DEPT table to the DEPT_EAST table. The DEPT_EAST table has already been created with the same structure (same columns and data types) as DEPT and is currently empty.

Solution

Use the INSERT statement followed by a query to produce the rows you want:

 1 insert into dept_east (deptno,dname,loc) 2 select deptno,dname,loc 3   from dept 4  where loc in ( 'NEW YORK','BOSTON' ) 

Discussion

Simply follow the INSERT statement with a query that returns the desired rows. If you want to copy all rows from the source table, exclude the WHERE clause from the query. Like a regular insert, you do not have to explicitly specify which columns you are inserting into. But if you do not specify your target columns, you must insert into all of the table's columns, and you must be mindful of the order of the values in the SELECT list as described earlier in "Inserting a New Record."