Results 1 to 5 of 5

Thread: Copy of a Table

  1. #1
    Join Date
    Oct 2003

    Unanswered: Copy of a Table

    I need to store the data from a view into a table.

    I am using this syntax to create the table and select the data from the view.

    Create table sch.t1 as (select * from sch.v1).

    This works in oracle but not in DB2.

    Can somebody help ?


  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    That would be a two-step process with DB2:
    create table sch.t1 like sch.v1;
    insert into sch.t1 select * from sch.v1;
    You can find more details here:
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2003
    thanks so much.

  4. #4
    Join Date
    May 2003
    You can also use the EXPORT and IMPORT commands. If you use IXF data format and use the REPLACE_CREATE option with the IMPORT, the table will be created if it does not already exist and the primary keys and indexes will be recreated the same as the source table (unlike CREATE TABLE LIKE ...). The only thing not created are the foreign keys (which is obvious since the other tables may not exist).

    If you are IMPORTing a file with a lot of rows, be sure to use the COMMITCOUNT option (1000 is fine) to do intermediate commits to avoid filling up the log.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Sep 2004
    db2 "create table xa1 as (select * from employee) definition only in DMS_16K_TS1 partitioning key...." SO on....

    This above command is more flexible to create a COPY of a table.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts