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

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

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

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

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.

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