Results 1 to 20 of 20

Thread: Creating a table by copying another table's structure

  1. #1
    Junior Member
    Join Date
    May 2006
    Answers
    9

    Creating a table by copying another table's structure

    How could I create a new table by copying another table's structure?


  2. #2
    Junior Member
    Join Date
    Apr 2006
    Answers
    24

    Re: Creating a table by copying another table's structure

    create table target tablename as (select * from source tablename);

    Patrick.


  3. #3
    Junior Member
    Join Date
    May 2006
    Answers
    9

    Re: Creating a table by copying another table's structure

    Thanks for the reply.

    So for example, the name of the first table is ABC, then the new table is DEF. The syntax would be:

    create table target DEF as (select * from ABC);

    Am I correct?


  4. #4
    Junior Member
    Join Date
    Jun 2006
    Answers
    2

    Re: Creating a table by copying another table's structure

    U can also use this command:

    CREATE TABLE [TableName] LIKE [SourceTableName]


  5. #5
    Contributing Member
    Join Date
    Sep 2005
    Answers
    91

    Re: Creating a table by copying another table's structure

    is it the same in Oracle and DB2... could some one explain in relats to different databases available...


  6. #6
    Junior Member
    Join Date
    Jun 2006
    Answers
    2

    Re: Creating a table by copying another table's structure

    This commands works for DB2...not sure about Oracle


  7. #7
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Creating a table by copying another table's structure

    Create table [tablename] like [sourcetablename]

    The above command does not work in oracle. But the following commands works in oracle

    create table DEF as (select * from abc);

    But an important thing is the above command copies the structure along with the data in table abc. That is, the above create table command creates a duplicate/backup table of source table.

    sql> create table abc (id number,name varchar2(20));
    table created.

    Sql> desc abc
    name null? type
    ----------------------------------------- -------- ----------------------------
    id number
    name varchar2(20)

    sql> insert into abc values (1, 'user1');
    1 row created.

    Sql> insert into abc values (2,'user2');
    1 row created.
    Sql> select * from abc;

    id name
    ---------- --------------------
    1 user1
    2 user2

    sql> create table def as (select * from abc);
    table created.
    Sql> desc def
    name null? type
    ----------------------------------------- -------- ----------------------------
    id number
    name varchar2(20)

    Sql> select * from def;
    id name
    ---------- --------------------
    1 user1
    2 user2


    In above sqls, we created abc table and inserted two records in it. Then we created def table by copying the structure of abc table. But this command also copies the data along with the structure. If your requirement is only to copy the structure then you need to follow a trick to filter records in the source table. The following is a simple trick to filter the records


    sql> create table def as (select * from abc where 1=2);
    table created. Sql> desc def name null? type ----------------------------------------- -------- ---------------------------- id number name varchar2(20) sql> select * from def; no rows selected
    we have added one where condition (1=2) in the inner select statement. It is obvious that 1 is not equal to 2 and this condition is applied on each column of the source table. Since 1 is not equal to 2 always all records in the source table are filtered. So the create table command that we have introduced later copies only the structure of the source table.

    Last edited by jamesravid; 06-19-2006 at 03:35 AM.

  8. #8
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Creating a table by copying another table's structure

    Create table [tablename] like [sourcetablename]

    the above command does not work in oracle. But the following commands works in oracle

    create table def as (select * from abc);

    but an important thing is the above command copies the structure along with the data in table abc. That is, the above create table command creates a duplicate/backup table of source table.


    sql> create table abc (id number,name varchar2(20));
    table created.

    Sql> desc abc
    name null? type
    ----------------------------------------- -------- ----------------------------
    id number
    name varchar2(20)

    sql> insert into abc values (1, 'user1');
    1 row created.

    Sql> insert into abc values (2,'user2');
    1 row created.

    Sql> select * from abc;
    id name ---------- --------------------
    1 user1
    2 user2

    sql> create table def as (select * from abc);
    table created.

    Sql> desc def
    name null? type
    ----------------------------------------- -------- ----------------------------
    id number
    name varchar2(20)

    sql> select * from def;
    id name
    ---------- --------------------
    1 user1
    2 user2


    In the above sqls, we created abc table and inserted two records in it. Then we created def table by copying the structure of abc table. But this command also copies the data along with the structure. If your requirement is only to copy the structure then you need to follow a trick to filter records in the source table. The following is a simple trick to filter the records,


    sql> create table def as (select * from abc where 1=2);
    table created.

    Sql> desc def
    name null? type
    ----------------------------------------- -------- ----------------------------
    id number
    name varchar2(20)

    sql> select * from def;
    no rows selected


    We have added one where condition (1=2) in the inner select statement. It is obvious that 1 is not equal to 2 and this condition is applied on each column of the source table. Since 1 is not equal to 2 always all records in the source table are filtered. So the create table command that we have introduced later copies only the structure of the source table.

    Thanks,
    James

    Last edited by jamesravid; 06-19-2006 at 04:15 AM.

  9. #9
    Junior Member
    Join Date
    Nov 2006
    Answers
    1

    Re: Creating a table by copying another table's structure

    Quote Originally Posted by NamrataSV View Post
    U can also use this command:

    CREATE TABLE [TableName] LIKE [SourceTableName]
    its wrong ...


  10. #10
    Administrator
    Join Date
    May 2006
    Answers
    331

    Re: Creating a table by copying another table's structure

    Quote Originally Posted by rakeshkamble View Post
    its wrong ...
    if you know the answer, please reply instead of just saying it's wrong...


  11. #11
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: Creating a table by copying another table's structure

    Quote Originally Posted by Beena View Post
    is it the same in Oracle and DB2... could some one explain in relats to different databases available...
    In Oracle database, a table can be created with another table's structure by using,
    CREATE TABLE emp_new AS SELECT * FROM emp;
    This will create the table with the records existing in emp table.
    To avoid the records, the following statement can be used,
    CREATE TABLE emp_new AS SELECT * FROM emp WHERE 1=2;

    In SQL Server 2000, a table can be created using another table, similar to create table statement of Oracle.
    There is another statement to create a table using SELECT statement.
    SELECT * INTO emp_new from emp where 1=2
    This will create the table structure alone.


  12. #12
    Junior Member
    Join Date
    Apr 2007
    Answers
    2

    Re: Creating a table by copying another table's structure

    create table new_Table_name as (select * from old_table_name where 1=2);

    It is copy only structure of old table.


  13. #13
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Creating a table by copying another table's structure

    create table new_table as select * from existing_table;

    the above code will copy both data and structure

    create table new_table as select * from existing_table where 1=2;

    the above code will copy only structure without data.

    NOTE--instead of using 1=2 ,you can use any statment that evalutes to false.


  14. #14
    Junior Member
    Join Date
    Mar 2009
    Answers
    7

    Thumbs up Re: Creating a table by copying another table's structure

    Quote Originally Posted by Allan Paul View Post
    Thanks for the reply.

    So for example, the name of the first table is ABC, then the new table is DEF. The syntax would be:

    create table target DEF as (select * from ABC);

    Am I correct?
    it should be...
    create table DEF as (select * from ABC);


  15. #15
    Junior Member
    Join Date
    Oct 2008
    Answers
    28

    Re: Creating a table by copying another table's structure

    Hi

    Try this following query

    create table as select *from
    where is null

    the above query copied only data structure from Data table name


  16. #16
    Junior Member
    Join Date
    Mar 2009
    Answers
    28

    Wink Re: Creating a table by copying another table's structure

    Try following query

    create table destination_table_name as (select * from source_table_name);

    eg:

    create table new_emp as (select * from emp);


  17. #17
    Junior Member
    Join Date
    Apr 2009
    Answers
    2

    Re: Creating a table by copying another table's structure

    create table emp1 as select * from emp where 1=2

    but my question how about the constraints , the above query not copied the constrains
    that are assigned to the emp table

    zamary shahab
    afghanistan , jalalabad city


  18. #18
    Contributing Member
    Join Date
    Dec 2008
    Answers
    76

    Re: Creating a table by copying another table's structure

    @zamary,

    Create table emp1 as select * from emp where 1=2
    See, here u are trying to create a new table "emp1" with same structure of what the "emp" table has with no data.

    But, as you said the above query will never copy the constraints of what "emp" table has.

    If u actually want to copy the data+structure+constraints, then u need to take backup of emp table and import it.
    Either by export/import.
    expdp/impdp-Oracle Database 10g Data Pump.


  19. #19
    Junior Member
    Join Date
    Dec 2007
    Answers
    26

    Re: Creating a table by copying another table's structure

    hi,
    there are 2 ways :

    first is : if u want to create a table by copying only structure of another table not data then use :
    create table as (select * from where 1=2)

    second is : if u want to create table with data and structure then use :
    create table as (select * from )


  20. #20
    Junior Member
    Join Date
    Nov 2006
    Answers
    6

    Re: Creating a table by copying another table's structure

    Create table newtablename as ( select * from oldtablename);
    this query will create a new table with same structure as the old one as well as all the records will be copied in the new table

    Last edited by kingrana; 04-17-2009 at 05:05 AM. Reason: explanation missed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact