I have 2 tables : A and B with same columns on both the table as :
Customer_Part_Number, Lear_Part_Number, Shipping_ID, Customer_Name, Effective_Date, End_Date,Change_ID, PO_Number, PO_Price I have successfully copied all the data to table B from table A But my scenario when the records of the columns :
TENANT_ID, CUSTOMER_PART_NUMBER, SHIPPING_ID, EFFECTIVE_DATE are same in table A,I don't want it to get it inserted to table B. I have tried using the query :
INSERT INTO OSUSR_1SV_QAD_PO_DATA (TENANT_ID, CUSTOMER_PART_NUMBER, LEAR_PART_NUMBER, SHIPPING_ID, CUSTOMER_NAME, PROGRAM_NAME, EFFECTIVE_DATE, END_DATE, CHANGE_ID, PO_NUMBER, PO_PRICE) SELECT TENANT_ID, CUSTOMER_PART_NUMBER, LEAR_PART_NUMBER, SHIPPING_ID, CUSTOMER_NAME, PROGRAM_NAME, EFFECTIVE_DATE, END_DATE, CHANGE_ID, PO_NUMBER, PO_PRICE FROM OSUSR_1SV_STAGING_FTP A WHERE not exists ( SELECT TENANT_ID, CUSTOMER_PART_NUMBER, LEAR_PART_NUMBER, SHIPPING_ID, CUSTOMER_NAME, PROGRAM_NAME, EFFECTIVE_DATE, END_DATE, CHANGE_ID, PO_NUMBER, PO_PRICE FROM OSUSR_1SV_QAD_PO_DATA B WHERE A.TENANT_ID = B.TENANT_ID and A.CUSTOMER_PART_NUMBER = A.CUSTOMER_PART_NUMBER and A.SHIPPING_ID = B.SHIPPING_ID and A.EFFECTIVE_DATE = B.EFFECTIVE_DATE ) Here, If the record of the columns TENANT_ID,CUSTOMER_PART_NUMBER,SHIPPING_ID,EFFECTIVE_DATE will be same, it wont be inserting the duplicate records.
Kindly help me out.
In the first Image there are duplicate data on table OSUSR_1SV_STAGING_FTP the basis of
TENANT_ID,
CUSTOMER_PART_NUMBER,
SHIPPING_ID,
EFFECTIVE_DATE
But I need to insert the data like Image 2 on table OSUSR_1SV_QAD_PO_DATA without duplication of TENANT_ID,
CUSTOMER_PART_NUMBER,
SHIPPING_ID,
EFFECTIVE_DATE
I have tried this query
MERGE OSUSR_1SV_QAD_PO_DATA A
USING OSUSR_1SV_STAGING_FTP B
ON (B.TENANT_ID = A.TENANT_ID and B.CUSTOMER_PART_NUMBER = A.CUSTOMER_PART_NUMBER
and B.SHIPPING_ID = A.SHIPPING_ID and B.EFFECTIVE_DATE = A.EFFECTIVE_DATE )
WHEN MATCHED THEN
DELETE WHERE ID NOT IN (SELECT MIN(ID)_
FROM OSUSR_1SV_STAGING_FTP
GROUP BY CUSTOMER_PART_NUMBER,SHIPPING_ID)
WHEN NOT MATCHED THEN
INSERT (TENANT_ID,CUSTOMER_PART_NUMBER,LEAR_PART_NUMBER,SHIPPING_ID,CUSTOMER_NAME,PROGRAM_NAME,EFFECTIVE_DATE,END_DATE,CHANGE_ID,PO_NUMBER,PO_PRICE)
VALUES(B.TENANT_ID,B.CUSTOMER_PART_NUMBER,B.LEAR_PART_NUMBER,B.SHIPPING_ID,
B.CUSTOMER_NAME,B.PROGRAM_NAME,B.EFFECTIVE_DATE,B.END_DATE,B.CHANGE_ID,B.PO_NUMBER,B.PO_PRICE);
