Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Create a New Table with SELECT Statements

By: FYIcenter.com

(Continued from previous topic...)

How To Create a New Table by Selecting Rows from Another Table?

Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the CREATE TABLE...AS SELECT statement to do this. Here is an example script:

>.\bin\sqlplus /nolog

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE emp_dept_10
  2  AS SELECT * FROM employees WHERE department_id=10;
Table created.

SQL> SELECT first_name, last_name, salary 
  2  FROM emp_dept_10;
FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Jennifer             Whalen                          4400

As you can see, this SQL scripts created a table called "emp_dept_10" using the same column definitions as the "employees" table and copied data rows of one department.

This is really a quick and easy way to create a table.

(Continued on next topic...)

  1. What Are DDL Statements?
  2. How To Create a New Table?
  3. How To Create a New Table by Selecting Rows from Another Table?
  4. How To Add a New Column to an Existing Table?
  5. How To Delete a Column in an Existing Table?
  6. How To Drop an Existing Table?
  7. How To Create a Table Index?
  8. How To Rename an Index?
  9. How To Drop an Existing Index?
  10. How To Create a New View?
  11. How To Drop an Existing View?

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...