Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    Nevada
    Posts
    14

    Unanswered: copying table data from one to another

    Hello,

    I need to transport data from one table to another , where both the tables have the same number and types of attributes. While inserting the data from table1 to table2 the data in table2 should be checked whether the data to be copied from table1 already exists in table2 or not. If it does, the record should not be copied or else that record needs to be inserted. The key is based on the column let's say "Deptno", which needs to be checked before inserting data from table1 to table2.

    I am using DB2 V7.2
    I am aware that this process can be completed using MERGE command in V8.1 . But as V7.2 does not support that command, how do we accomplish it in V7.2

    I would be very thankful if you anyone can help me out in doing this.

    Thanks,
    Raj

  2. #2
    Join Date
    Feb 2002
    Posts
    96
    Export records from table1.
    Import from table1.del of del insert into table2;

    Here, existing records in table2 will not be overwritten. So existing key records will be rejected & new records will be inserted.

    Regards
    Prakash

  3. #3
    Join Date
    Sep 2003
    Location
    Nevada
    Posts
    14

    copy all the records that doesn't exist

    Thank you prakash for the solution.

    so through this method,
    will all the records be checked for the existance in table2? how is this statement checking the keys for the existance of records as i don't see any conditions...

    what is .del in the statements mentioned?

    Thank you in advance,
    Raj

  4. #4
    Join Date
    Feb 2002
    Posts
    96
    It will check the primary key or unique index in table. You donot need to specify any extra condition in IMPORT st. Just make sure your primary key exists in table. So the duplicate record will be rejected during IMPORT.

    .del is the file type. Plse go thru the COMMAND REFERENCE for details. Use the filetype(del, ixf, etc) according to your requirement.

    Regards
    Prakash

  5. #5
    Join Date
    Sep 2003
    Posts
    85
    Another option is to:
    DECLARE cursorname CURSOR FOR SELECT * FROM sourcetable;
    LOAD FROM cursorname OF CURSOR INSERT INTO targettable NONRECOVERABLE ALLOW READ ACCESS;

    The LOAD will also kick out any duplicates. You can also set it up via the LOAD command to capture the duplicate rows into a table.

  6. #6
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Question

    I have an identity(auto number) column. I would like it be imported and the identity column should increment from where it left off. How can this be accomplished.

    Thanks in Advance,
    Newebie

Posting Permissions

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