Hi;
I have two tables exactly same but in two different DB schema. the Table_1 has a primary key SU_ID which gets assigned by system while submitting the value thru online application. I am doing an INSERT INTO command like:

INSERT INTO TABLE_1 SELECT * FROM TABLE_2.

the problem is that TABLE_2 has SU_ID which is unique and TABLE_2 has same value.

How to resolve this issue? Please advice?

Why would you want to insert duplicate values into table_1?

TABLE_1 has old data, TABLE_2 has the updated datab but the SU_ID is unique to each table and it is primary key

Please try to explain what is it that you wish to accomplish. Do you want to update rows in table_1 with new values in table_2, or add rows from table_2 to table_1? In the latter case you will have to generate unique key values yourself.

Sorry for the confusion. Let me explain what I want.

I have TABLE_1 which has updated value where SU_ID is primary key. the value in SU_ID is system generated 12 byte number. This table has tons of rows.

I have another TABLE_2 which has exactly same structure but has different value(old data).

How come I insert the new data into TABLE_2 from TABLE_1?

I know that INSERT INTO TABLE_2 SELECT * FROM TABLE_1 will not work because TABLE_2 might have values in SU_ID which may be in TABLE_1.

I was thinking that if I first find out a Max(SU_ID) in TABLE_2 and then keep increamenting by one for the next row then it may work but I don't know how?

Many Thanks.
while running my query in Mainframe using QMF tool:

SELECT
ROW_NUMBER() OVER () + <MAXIMUM SU_ID>,
OVERRIDE_FLAG,EFFECTIVE_DATE,END_DATE,PARM_VALUE
FROM MTVDV1.JOB_PARM_DETAIL;

it is throwing an SQL error like "SQL error at or before < (line 2, position 25)."


Can you explain why?

Could be something like this:

INSERT INTO TABLE_1
SELECT * FROM TABLE_2
where SU_ID not in (select SU_ID from TABLE_1)
;
Lenny

Can you explain why you have replaced "<rest of columns>" with "OVERRIDE_FLAG,EFFECTIVE_DATE,END_DATE,PARM_VALUE" , but only removed "value" from "<maximum su_id value>"?

I thought that "<rest of columns>" should be replaced by the actual columns name in the table. in the same way I wrote for "<maximum su_id value>" and removed "value"

Is this is a mistake? do I need to run the SQL the way you have written the sql?

You can use MERGE.

Cara.

How about replacing "<maximum su_id value>" with the actual maximum su_id value?

What is your DB2 version on z/OS? If you are in V9.1 it can be done through merge.