Methods for importing records from remote Oracle that do not exist on the same table in the local

Source: Internet
Author: User

Scenario: There is a table A on the remote Oracle, and a table B with the same table structure exists locally. Due to the fact that a few records from the business system operation were saved in the table B, and some of the records in a were originally imported, none of the records in a are saved. There are 15 records in A, B holds 3 records in A and B has 4 records generated by the local business system, and the 4 records generated by the business system in B cannot be deleted. Now I want to import the other 12 records from a into B, and I can keep the original 4 records from the business system in B.

Resolve sql:

1, first in a to get the record in B appears.

SELECT * FROM [email protected]_name a Where exists

(SELECT * from b b wherer b.colume1=a.colume1 and b.colume2=a.colume2 and B.colume3=a.colume3)

2. Obtain a record exists in B that has not been replaced by the not exists;

3. Insert a record that does not appear in B

INSERT INTO B

SELECT * FROM [e-mail Protected]_name a where NOT exists

(SELECT * from b b wherer b.colume1=a.colume1 and b.colume2=a.colume2 and B.colume3=a.colume3);

Note: Using this method to import data avoids importing duplicate records (in the absence of a primary key constraint in the table). If there is a PRIMARY key constraint on a field in the table, the primary KEY constraint is violated if it is not imported in a way that distinguishes it from the nonexistent data.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.