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.
