How can I create a table from another table without inserting rows (Only want columns) ORACLE SQL?

Question

I'm trying to create a table from another table (CREATE TABLE...AS... statement), but I don't want to insert the rows from the first table. I only need the columns. How?

CREATE TABLE employees24
AS (
    SELECT employee_id AS "ID",
      first_name,
      last_name,
      salary,
      department_id AS "DEPT_ID"
    FROM employees);

You can remove the brackets – Bohemian Sep 27 '16 at 01:25 — Bohemian, Sep 27 '16 at 01:25

score 2 · Answer 1 · answered Sep 26 '16 at 22:53

2

Something like create table employees24 as (select * from employees where 0 = 1); should work.

answered Sep 26 '16 at 22:53

thanks alot. i was wondering around and finally found the desired solution to the problem – Jency Nov 17 '22 at 12:42

score 1 · Answer 2 · answered Sep 26 '16 at 22:53

To create an empty table you just need to pass a select, that will not return any rows:

CREATE TABLE employees24
AS (
    SELECT employee_id AS "ID",
      first_name,
      last_name,
      salary,
      department_id AS "DEPT_ID"
    FROM employees
    WHERE 1=2);

How can I create a table from another table without inserting rows (Only want columns) ORACLE SQL?

2 Answers2