Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2011
    Posts
    3

    Unanswered: Create a table from another table with data

    Hi All,

    I am new t0 Db2.
    I am creating a table from another table as below:

    Create table New Like Old

    Insert into New select * from old

    Is there any better way than doing above.

    I am using below query which is not working in DB2

    Create table new as select * from old;

    Please let me know if there are any better options.

    Thanks,
    Mrcool

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I realize that having to submit two SQL statements (one to clone the table, and another insert the data from the other table) means your life is most likely ruined, but I don't think you can do this with one SQL statement in DB2 (unless there is a new feature I don't know about).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2011
    Posts
    3
    Thanks Marcus. But is there any other approach than what I am doing.

    Thanks,
    Mrcool

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The only other way I know of is to EXPORT the data from first table using IXF format, and then IMPORT into a new table using the REPLACE_CREATE option. This will create the table using the table definition contained in the export file (IXF format only) if the table does not already exist. If you have more than 50,000 rows, I would use the COMMITCOUNT option on the IMPORT.
    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
    Jan 2011
    Posts
    3
    Once Again Thanks for your inputs Marcus. It is helpfull.

    Cheers
    Mrcool

  6. #6
    Join Date
    Jan 2010
    Posts
    335
    With load you could bypass the transaction log.
    declare load_cur cursor for select * from old;
    load from load_cur of cursor insert into new nonrecoverable;

    But that would be 3 Statements. On the other hand it's a lot faster than Import or Insert for large amonut of data.

    Caution! Nonrecoverable means that the data is not recoverable in case of restore.
    The "create table ... like ..." does not copy any Indexes, Constraints etc.

    Cheers
    nvk

  7. #7
    Join Date
    Oct 2011
    Posts
    2
    Simply use the "with data" option"
    For example
    create table test as (select * from xxxusersxxx) with data

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    create table test as (select * from xxxusersxxx) with data

    was this tested ?? there is a mandatory option : with no data according the syntax diagram - not seen this option before
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  9. #9
    Join Date
    Oct 2011
    Posts
    2
    I was actually using it when I typed the message. So I can say that I've tested it.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Looking into manuals,
    DB2 for iSeries(at least V5R4) supports WITH DATA in as-subquery-clause, like...
    AS (select-statement) WITH DATA

    DB2 10 for z/OS and DB2 9.7 for LUW don't support WITH DATA.

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That gives me an error: (DB2 LUW 9.5)

    Code:
    DB2INST1:LCAD-->create table andy.call_Center as (select * from production.call_center) with data
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "data" was found following "on.call_center)
    with".  Expected tokens may include:  "NO".  SQLSTATE=42601
    I have checked the syntax diagrams for LUW 9.5 and 9.7 and neither have this option. They only have "WITH NO DATA". I even check DB2 for Z/OS and the syntax does not show it.

    Andy

Posting Permissions

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