跨数据库同表数据追加：基于指定字段唯一性的同步需求

Got it, let's break down how to solve this problem. The core idea is to selectively insert rows from A into B only where the rows don't already exist in B based on your specified unique fields. Here's how to do it step by step:

1. Define Your Unique Key(s) #

First, clarify which field(s) determine uniqueness for each table. For your People example, this could be:

• A single field like ID (if duplicate IDs aren't allowed, regardless of other values)
• A combination like name + Surname (if rows with the same full name should be considered duplicates)

2. Use INSERT ... SELECT with a Filter #

The most straightforward way is to pair an INSERT statement with a SELECT that excludes existing rows using either NOT EXISTS or a LEFT JOIN.

Example 1: Unique Key is ID #

If you want to append rows from A.People to B.People only when the ID isn't already present in B:

INSERT INTO B.People (ID, name, Surname)
SELECT ID, name, Surname
FROM A.People
WHERE NOT EXISTS (
    SELECT 1 
    FROM B.People
    WHERE B.People.ID = A.People.ID
);

In your sample data, this would not append any rows because all IDs from A (1,2,3) already exist in B (even though some have different names).

Example 2: Unique Key is name + Surname #

If you want to avoid duplicate full names (regardless of ID):

INSERT INTO B.People (ID, name, Surname)
SELECT ID, name, Surname
FROM A.People
WHERE NOT EXISTS (
    SELECT 1 
    FROM B.People
    WHERE B.People.name = A.People.name
    AND B.People.Surname = A.People.Surname
);

In your sample data, this would only append the row 1 Mark Anthony—since Julius Ceasar and Marcus Crassus already exist in B.

Alternative: LEFT JOIN Approach #

You can also use a LEFT JOIN to filter out existing rows:

INSERT INTO B.People (ID, name, Surname)
SELECT A.People.ID, A.People.name, A.People.Surname
FROM A.People
LEFT JOIN B.People 
    ON B.People.name = A.People.name 
    AND B.People.Surname = A.People.Surname -- adjust to your unique keys
WHERE B.People.ID IS NULL; -- checks for non-matching rows

3. Important Considerations #

• Test First: Always run the SELECT part alone first to verify you're getting the correct rows before executing the INSERT.
• Indexes: For large tables, add indexes on your unique key fields in B to speed up the existence check (this will make the query run much faster).
• Permissions: Ensure your database user has SELECT access to A and INSERT access to B.
• Automate for Multiple Tables: If you need to do this for all tables (since they have identical schemas), you can generate dynamic SQL using your database's system tables (e.g., INFORMATION_SCHEMA.TABLES in MySQL/SQL Server) to loop through all tables and run the insert logic.

内容的提问来源于stack exchange，提问作者Vahagn Tumanyan