How To Create a New Table by Selecting Rows from Another Table?
Page 1 of 1
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 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.
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 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.
Similar topics
» How To Create a Table Index?
» How To Create a Testing Table?
» How To Select Some Rows from a Table?
» How To Select All Columns of All Rows from a Table?
» How To Create a New Table?
» How To Create a Testing Table?
» How To Select Some Rows from a Table?
» How To Select All Columns of All Rows from a Table?
» How To Create a New Table?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
Sun Jan 31, 2010 12:35 am by Micheal
» Accessing tables of other users in SQL
Sun Jan 31, 2010 12:34 am by Micheal
» Grouping commands in SQL
Sun Jan 31, 2010 12:32 am by Micheal
» Operations on Result Sets in SQL
Sun Jan 31, 2010 12:31 am by Micheal
» Subqueries in SQL
Sun Jan 31, 2010 12:30 am by Micheal
» Joining Relations in SQL
Sun Jan 31, 2010 12:28 am by Micheal
» Delete comand in SQL
Sun Jan 31, 2010 12:27 am by Micheal
» Update command in SQL
Sun Jan 31, 2010 12:26 am by Micheal
» Insert command in SQL
Sun Jan 31, 2010 12:23 am by Micheal