Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: transfer (insert) data from one table to another table

    2 tables have almost same structure but table2 has additional col - PROD_ID. How do I transfer data from table1 to table2 and set PROD_ID to 'XXX' at the same time? This what i have but i don't think it's right:

    insert data into DBA.table2
    from (
    PROD_ID = ‘XXX’
    , select * from DBA.XXX_table1);

    thanks in advance
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Dec 2008
    Posts
    76
    insert into table2(prod_id, a,b,c,d) select 'xxx', a, b, c, d from table1
    Last edited by rdutton; 04-22-09 at 13:22.

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by rdutton
    insert into table2(prod_id, a,b,c,d) select 'xxx', a, b, c, d from table1
    I did not want to list all cols from table1 as there are many, but it is ok. Thanks so much
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If columns of table2 are same as table1 except adding prod_id at the first or last, you may want to do like:

    insert into table2 select 'xxx', t1.* from table1 t1
    or
    insert into table2 select t1.*, 'xxx' from table1 t1

  5. #5
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    it did work. thanks

    now there are 32,000,000 rows in table1 so when i submit this statement i run into a problem:

    During SQL processing it returned: SQL0964C The transaction log for the database is full. SQLSTATE=57011

    I increased db cfg params:
    LOGFILSIZ = 4096 (from 1024)
    LOGPRIMARY) = 60 (from 20)
    LOGSECOND) = 40 (from 10)

    but still getting this error. And I understand I can not commit while I am doing inserts. How do I resolve this?
    Thanks
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can use an EXPORT to export the data, then use IMPORT with COMMITCOUNT to insert the data.

    Andy

  7. #7
    Join Date
    Sep 2004
    Posts
    12
    You can use Load from cursor.

    declare mycur cursor for select XXX,..... from tab1
    load from mycur of cursor insert into tab2 nonrecoverable

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Since V8 DB2 has "MERGE". Answers to a lot of problems, also this one

Posting Permissions

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