Auto Naming In Oracle DataBase when inserting to table

Question

I have two table: table_1 and table_2. In these tables there are two columns: p_code (varchar2) and o_code (varchar2) and both of them primary key. So we have:

table_1.p_code,
table_1.o_code,
table_2.p_code,
table_2.o_code

I want to copy table_2 into table_1 but there may be some same rows that already exist in table_1. I thought i could handle this situation with function or procedure but i couldnt. How can i handle this?

By the way: Two Example table and columns:

Table_1:
P_code    O_code
C123      PREP100
C123      PREP101

Table_2:
P_code    O_code
C123      PREP101
C123      PREP102

I want to insert table_2 into Table_1 but C123 PREP already exist in Table_1. I thought i could sperate the last three char, trun into number, increase one, turn into varchar2, and see if exist in table_1. But i couldnt write the sql procedure or function for it...

Answer 1

You could use something like:

insert into table_1 (p_code, o_code)
(-- add all rows in table_2 that are not present in table_1
 (select t2.p_code, t2.o_code from table_2 t2 
 minus
 select t1.p_code, t1.o_code from table_1 t1)
 union all
 -- add all rows in table_2 that are present in table_1
 (select t2.p_code, t2.o_code || '_2' from table_2 t2
  join table_1 t1a on t2.p_code = t1a.p_code and t2.o_code = t1a.o_code)
);

This will insert the new rows unchanged and suffix the existing rows with _2; you could then easily run an UPDATE statement afterwards to generate unique Ids, or (preferably) use a sequence to generate the new IDs in the first place.

Answer 2

This should work in most SQL engines (SQLFiddle Demo for Oracle 11G):

INSERT INTO table_1 (p_code, o_code)
SELECT p_code, o_code FROM table_2
MINUS
SELECT p_code, o_code FROM table_1

By your example I assume that your primary key is (p_code, o_code).

UPDATE: SQL92 has standard EXCEPT operator, but Oracle does not support it. Instead, it uses MINUS, which works exactly the same.