CODEWITHSUNDEEP
oracle-database
iPhoneJavaDev
iPhoneJavaDev

Reputation: 825

Insert new record if record does not exist, duplicate record if record exist

I'd like to insert a new record in table A if there is no existing record in table A for my customer. If there's already an existing record in table A for my customer, I'd like to duplicate it and create a new record.

I was thinking of using MERGE but found out it should be UPDATE and INSERT, not INSERT and INSERT.

please advise.

TABLE A

ID            CUSTOMER_ID            DATA
1             104                    Data1
2             104                    Data2

In the table above, say I have customer_id=105. Since it's not in the table, i will insert a new record for customer_id=105:

3             105                    Data3

If I have customer_id=104, I will duplicate the maximum record for the customer since it exists in the table:

4             104                    Data2

Upvotes: 0

Views: 116

Answers (1)

phonetic_man
phonetic_man

Reputation: 1088

Assuming that you don't want to completely duplicate the record and change values of some columns when inserting the duplicate record I have tried to create the following procedure. I have used the employee table as an example. If this works for you then replace the employee with customer.

CREATE OR REPLACE PROCEDURE prc_insert_emp (p_empno IN NUMBER)
AS 
    v_exists NUMBER;
BEGIN
   SELECT 1 INTO v_exists
   FROM  emp
   WHERE empno = p_empno;

   --Duplicate the record when employee is found
   INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
   SELECT 1111, ename, job, mgr, hiredate, sal, comm, deptno
   FROM emp
   WHERE empno = p_empno; 

   DBMS_OUTPUT.PUT_LINE('Employee found');

EXCEPTION
    WHEN NO_DATA_FOUND THEN
   --Insert values when the record is not found
        INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
        VALUES (5599, 'KING', 'MANAGER', 7839,  SYSDATE, 5000, 50, 10);
        DBMS_OUTPUT.PUT_LINE('Employee not found');
END;

You can invoke this procedure and pass the relevant parameter (which may be customer id in your case). Further, you can add an out parameter which will return a flag to signify if the customer was duplicated or newly inserted.

If you want to do it in a single statement try this.

INSERT INTO CUST
SELECT MAX(id)+1,
       customer_id,
       'DATA2' FROM CUST
WHERE customer_id = 104
GROUP BY customer_id
UNION ALL
SELECT 5, 
       104,
       'DATA2' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM cust WHERE customer_id = 104);

In the above statement if the customer 104 is found then the query above union all will be executed (it will duplicate the record for customer id 104). The query below UNION ALL will not be executed because of the NOT EXISTS condition.

If the customer is not found then the query above UNION ALL will not return any rows. However, the query below UNION ALL will be executed and you can insert the new record with the fresh customer id.

Upvotes: 1

Related Questions