#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Location
    Calgary, Alberta, Canada
    Posts
    52
    Rep Power
    11

    Copying tables BUT not data


    How can I create a copy of a table without copying the data from the origional table
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    CREATE TABLE new_table
    AS
    SELECT
    * FROM old_table
    WHERE 1=2;

    But this will only recreate the structure of the table. Primary keys, foreign keys or other table constraints will not be copied.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Location
    Calgary, Alberta, Canada
    Posts
    52
    Rep Power
    11
    Originally Posted by shammat
    CREATE TABLE new_table
    AS
    SELECT
    * FROM old_table
    WHERE 1=2;

    But this will only recreate the structure of the table. Primary keys, foreign keys or other table constraints will not be copied.
    Thanks for the help!

    Do you know if there is a way to copy the columns over but not their data or definitions?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    18
    Rep Power
    0
    Do the following :

    Create new_table as
    Select * from old_table where
    rownum <1

    This will give you an empty new table with the identical structure of the old table.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    Originally Posted by gdjsmith
    Do you know if there is a way to copy the columns over but not their data or definitions?
    What do you mean with that? My statement (ninakot's which is essentially the same as mine) will create a table with the same columns as the old one. No data will be copied.

    A column needs a definition, so what would you want to copy if you omit the definition?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    11
    Good Day,

    CREATE TABLE <table characteristics> AS SELECT <query> is generally known as CTAS.
    When you create a table using the subquery, only the NOT NULL constraints associated with the columns are copied to the new table. Other constraints and column default definitions are not copied.

    Regards,
    Dan

IMN logo majestic logo threadwatch logo seochat tools logo