同一SQL Server下跨数据库表增量数据同步实现方法咨询

Got it, let's tackle this problem step by step—first we'll sync the missing records (4 and 5) right now, then set up a system to handle future incremental changes automatically.

To get the existing missing data into database2.Table2 immediately, use a simple INSERT ... SELECT query with a join to exclude records already present in the target table. You'll need to use your table's primary key (or a unique identifier column) to match records:

-- Replace Column1, Column2, PrimaryKeyColumn with your actual column names
INSERT INTO database2.dbo.Table2 (PrimaryKeyColumn, Column1, Column2, ...)
SELECT t1.PrimaryKeyColumn, t1.Column1, t1.Column2, ...
FROM database1.dbo.Table1 t1
LEFT JOIN database2.dbo.Table2 t2 
  ON t1.PrimaryKeyColumn = t2.PrimaryKeyColumn
WHERE t2.PrimaryKeyColumn IS NULL;

This query pulls all records from Table1 that don't exist in Table2 and inserts them—this will add the 4 and 5 records you're missing.

Now for the long-term solution: you need to automatically sync new or updated records as they happen in Table1. Here are three common, reliable approaches depending on your needs:

Option 1: Triggers (Real-Time Sync) #

Triggers run immediately when an INSERT or UPDATE happens on Table1, making this a real-time solution. They're great for small to medium tables where you need instant sync:

-- Trigger for new records
CREATE TRIGGER trg_Table1_Insert
ON database1.dbo.Table1
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON; -- Prevents extra result sets from interfering with app logic
    
    -- Insert new records into Table2 if they don't already exist
    INSERT INTO database2.dbo.Table2 (PrimaryKeyColumn, Column1, Column2, ...)
    SELECT PrimaryKeyColumn, Column1, Column2, ...
    FROM inserted
    WHERE NOT EXISTS (
        SELECT 1 FROM database2.dbo.Table2 t2 
        WHERE t2.PrimaryKeyColumn = inserted.PrimaryKeyColumn
    );
END;
GO

-- Trigger for updated records
CREATE TRIGGER trg_Table1_Update
ON database1.dbo.Table1
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Update matching records in Table2
    UPDATE t2
    SET t2.Column1 = i.Column1,
        t2.Column2 = i.Column2,
        -- Add all other columns you need to sync here
        t2.LastUpdated = i.LastUpdated -- If you have an update timestamp column
    FROM database2.dbo.Table2 t2
    INNER JOIN inserted i 
      ON t2.PrimaryKeyColumn = i.PrimaryKeyColumn;
END;
GO

Note: Triggers add overhead to write operations on Table1, so avoid them if your table has extremely high write volumes.

Option 2: SQL Server Agent Job (Scheduled Sync) #

If you don't need real-time sync (e.g., sync every 5/15 minutes), a scheduled job is a lightweight option. First, add an update timestamp column to Table1 if you don't have one:

ALTER TABLE database1.dbo.Table1 
ADD LastUpdated DATETIME DEFAULT GETDATE() NOT NULL;

Then create a sync script using MERGE to handle both inserts and updates:

MERGE INTO database2.dbo.Table2 t2
USING (
    -- Get all records in Table1 that are new or updated since the last sync
    SELECT PrimaryKeyColumn, Column1, Column2, LastUpdated
    FROM database1.dbo.Table1
    WHERE LastUpdated > (
        SELECT ISNULL(MAX(LastUpdated), '1900-01-01') 
        FROM database2.dbo.Table2
    )
) t1 ON t2.PrimaryKeyColumn = t1.PrimaryKeyColumn
WHEN MATCHED THEN
    -- Update existing records with changes
    UPDATE SET
        t2.Column1 = t1.Column1,
        t2.Column2 = t1.Column2,
        t2.LastUpdated = t1.LastUpdated
WHEN NOT MATCHED THEN
    -- Insert new records
    INSERT (PrimaryKeyColumn, Column1, Column2, LastUpdated)
    VALUES (t1.PrimaryKeyColumn, t1.Column1, t1.Column2, t1.LastUpdated);

Finally, set up a SQL Server Agent Job to run this script on your desired schedule (e.g., every 10 minutes). This is ideal for larger tables where real-time sync isn't critical.

Option 3: Change Data Capture (CDC) (Enterprise-Grade Sync) #

If you're using SQL Server Enterprise Edition, CDC is a built-in feature that tracks all changes to Table1 (inserts, updates, deletes) without adding trigger overhead. It's perfect for complex sync scenarios where you need to audit changes or sync with multiple targets.

1. Enable CDC at the database level:

ALTER DATABASE database1 
SET CHANGE_TRACKING = ON (
    CHANGE_RETENTION = 2 DAYS, -- How long to keep change history
    AUTO_CLEANUP = ON -- Auto-delete old change records
);

2. Enable CDC on Table1:

ALTER TABLE database1.dbo.Table1 
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);

3. You can then query the change tracking tables to get incremental changes and sync them to Table2 (you can wrap this logic in a stored procedure and schedule it with SQL Server Agent, or use ETL tools like SSIS to handle the sync).

• Always test sync scripts in a non-production environment first to avoid data issues.
• For delete operations (if you need to sync those too), add logic for deletes in your chosen method (e.g., a delete trigger, or include deletes in your CDC sync).

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