How to duplicate a table via CTAS (Oracle)

Configurare noua (How To)

Situatie

Duplicating a table via CTAS (Create Table As Select).

Using CTAS you can duplicate a table by keeping also only specific columns.

Solutie

Pasi de urmat

Syntax :

CREATE TABLE new_table_name AS
SELECT *
FROM   table_name; 

 

CREATE TABLE new_table_name AS
SELECT column_name1,
column_name2,
column_name3,
column_name5,
column_name9
FROM   table_name; 

Sample :

CREATE TABLE hr.employees_duplicate AS
SELECT *
FROM   hr.employees; 

 

CREATE TABLE hr.employees_duplicate_small AS
SELECT employee_id,
first_name,
last_name,
email,
salary
FROM   hr.employees; 

 

SELECT table_name,
Count(*) AS COLUMNS
FROM   dba_tab_cols
WHERE  table_name IN ( ‘EMPLOYEES’, ‘EMPLOYEES_DUPLICATE’,‘EMPLOYEES_DUPLICATE_SMALL’)
GROUP  BY table_name; 

 

SELECT Count(*)
FROM   hr.employees;

SELECT Count(*)
FROM   hr.employees_duplicate;

SELECT Count(*)
FROM   hr.employees_duplicate_small; 

 

Output :

(12.2.0.1) SQL> CREATE TABLE HR.EMPLOYEES_DUPLICATE AS SELECT * FROM HR.EMPLOYEES;

Table created.

(12.2.0.1) SQL> CREATE TABLE HR.EMPLOYEES_DUPLICATE_SMALL AS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, SALARY FROM HR.EMPLOYEES;

Table created.

(12.2.0.1) SQL> SELECT TABLE_NAME, count(*) as columns FROM dba_tab_cols WHERE TABLE_NAME in ('EMPLOYEES','EMPLOYEES_DUPLICATE','EMPLOYEES_DUPLICATE_SMALL') GROUP BY TABLE_NAME;(12.2.0.1) SQL> (12.2.0.1) SQL>

TABLE_NAME                               COLUMNS
---------------------------------------- ----------
EMPLOYEES                                11
EMPLOYEES_DUPLICATE                      11
EMPLOYEES_DUPLICATE_SMALL                5

(12.2.0.1) SQL> SELECT count(*) FROM HR.EMPLOYEES;

COUNT(*)
----------
107

(12.2.0.1) SQL> SELECT count(*) FROM HR.EMPLOYEES_DUPLICATE;

COUNT(*)
----------
107

(12.2.0.1) SQL> SELECT count(*) FROM HR.EMPLOYEES_DUPLICATE_SMALL;

COUNT(*)
----------
107

(12.2.0.1) SQL>

Tip solutie

Permanent

Voteaza

(2 din 7 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?