如何高效复制外键关联表中满足条件的关联数据

Great question—using cursors for this kind of task is definitely not the most efficient approach, since databases excel at set-based operations instead of row-by-row processing. Here's a much faster, scalable solution using temporary mapping tables to track the relationship between old and new primary keys as you copy data across tables.

The Core Idea #

We'll create temporary tables to store mappings between the original IDs (from your existing data) and the new auto-generated IDs (from the copied records). This lets us maintain foreign key relationships as we bulk-insert data into each related table.

Let's walk through this step-by-step using your schema:

Step 1: Copy Customers & Track ID Mappings #

First, we'll copy the qualifying customers (age > 18) and record which original CustomerID maps to each new CustomerID.

SQL Server Example #

-- Create a temp table to hold old → new CustomerID mappings
CREATE TABLE #CustomerMap (OldCustomerID INT, NewCustomerID INT);

-- Insert qualifying customers, and populate the mapping table
INSERT INTO Customers (Name, Age)
OUTPUT src.CustomerID, inserted.CustomerID INTO #CustomerMap
SELECT Name, Age 
FROM Customers src 
WHERE Age > 18;

PostgreSQL Example #

PostgreSQL uses the RETURNING clause to capture new IDs, so we'll use a CTE to insert and map in one step:

-- Create a permanent temp table (or use a CTE for shorter scope)
CREATE TEMP TABLE CustomerMap (OldCustomerID INT, NewCustomerID INT);

WITH InsertedCustomers AS (
    INSERT INTO Customers (Name, Age)
    SELECT Name, Age FROM Customers WHERE Age > 18
    RETURNING CustomerID AS NewCustomerID, Name, Age
)
INSERT INTO CustomerMap (OldCustomerID, NewCustomerID)
SELECT old.CustomerID, new.NewCustomerID
FROM Customers old
JOIN InsertedCustomers new 
    ON old.Name = new.Name AND old.Age = new.Age;

Step 2: Copy Invoices Using the Customer Mapping #

Next, we'll copy invoices linked to the original customers, replacing the old CustomerID with the new one from our mapping table. We'll also track old → new InvoiceID mappings for the LineItems.

SQL Server Example #

CREATE TABLE #InvoiceMap (OldInvoiceID INT, NewInvoiceID INT);

INSERT INTO Invoice (CustomerID, Date)
OUTPUT src.InvoiceID, inserted.InvoiceID INTO #InvoiceMap
SELECT cm.NewCustomerID, src.Date
FROM Invoice src
JOIN #CustomerMap cm 
    ON src.CustomerID = cm.OldCustomerID;

PostgreSQL Example #

CREATE TEMP TABLE InvoiceMap (OldInvoiceID INT, NewInvoiceID INT);

WITH InsertedInvoices AS (
    INSERT INTO Invoice (CustomerID, Date)
    SELECT cm.NewCustomerID, i.Date
    FROM Invoice i
    JOIN CustomerMap cm ON i.CustomerID = cm.OldCustomerID
    RETURNING InvoiceID AS NewInvoiceID, CustomerID, Date
)
INSERT INTO InvoiceMap (OldInvoiceID, NewInvoiceID)
SELECT old.InvoiceID, new.NewInvoiceID
FROM Invoice old
JOIN InsertedInvoices new 
    ON old.Date = new.Date
    AND old.CustomerID = (SELECT cm.OldCustomerID FROM CustomerMap cm WHERE cm.NewCustomerID = new.CustomerID);

Step 3: Copy LineItems Using the Invoice Mapping #

Finally, we'll copy LineItems linked to the original invoices, using the invoice mapping table to point to the new InvoiceIDs.

All Databases (Generalized) #

INSERT INTO LineItem (InvoiceID, Item, Price, Qty)
SELECT im.NewInvoiceID, src.Item, src.Price, src.Qty
FROM LineItem src
JOIN #InvoiceMap im  -- Or InvoiceMap for PostgreSQL
    ON src.InvoiceID = im.OldInvoiceID;

Cleanup (Optional) #

Don't forget to drop your temporary tables when you're done:

-- SQL Server
DROP TABLE #CustomerMap;
DROP TABLE #InvoiceMap;

-- PostgreSQL
DROP TABLE CustomerMap;
DROP TABLE InvoiceMap;

Why This Is Better Than Cursors #

• Set-based operations: Databases are optimized for bulk processing, so this will run orders of magnitude faster than looping through each row with a cursor.
• Maintains referential integrity: The mapping tables ensure every copied record links correctly to its parent in the new data set.
• Scalability: This works just as well for 100 records as it does for 100,000—no performance degradation from row-by-row processing.

Notes to Consider #

• If your tables have unique constraints (e.g., a unique Name for customers), adjust the mapping logic to use a unique identifier from the source data (like a combination of fields) to ensure accurate old→new ID matching.
• For MySQL, you can use 167432 with bulk inserts, but it's less straightforward—using a temporary table with INSERT ... SELECT and joining on source data fields is the most reliable approach.

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