SQL Server：如何从链接服务器只读View同步全量历史数据至本地数据库Table

Hey there, let's tackle this problem step by step—since you're stuck with read-only access to that remote view (which only shows the latest 100 events) and need to build a complete local history, here are some practical, workable approaches tailored to your constraints:

First, let's rule out the trigger idea upfront #

Triggers aren't going to work here for a simple reason: you can't create triggers on the remote server (you only have read-only access), and local SQL Server triggers can't monitor changes to a remote view. Triggers only fire in response to INSERT/UPDATE/DELETE operations on local tables, so there's no way to trigger a copy when the remote view updates. So we can cross that off the list.

Optimized Scheduled Stored Procedure (Your Original Idea, Fixed) #

Your initial thought about a scheduled stored procedure is valid—you just need to tweak it to avoid missing data when events roll off the 100-entry view. Here's how to make it reliable:

Key Assumption: The remote view has a unique, incrementing identifier or timestamp #

Most event tables/views have something like an EventID (auto-incrementing) or a precise EventTimestamp that uniquely identifies each record and lets you tell which are newer than what you've already copied.

Example Implementation:

1. First, make sure your local history table (LocalEventHistory) includes that unique identifier/timestamp as a primary key or unique index.
2. Write a stored procedure that only pulls records you haven't already saved:

   CREATE PROCEDURE dbo.PullRemoteEventHistory
   AS
   BEGIN
       SET NOCOUNT ON;
   
       -- Get the latest EventID we already have locally
       DECLARE @LastCapturedEventID INT;
       SELECT @LastCapturedEventID = ISNULL(MAX(EventID), 0) FROM dbo.LocalEventHistory;
   
       -- Insert only new events from the remote view
       INSERT INTO dbo.LocalEventHistory (EventID, EventTimestamp, EventDetails, [OtherFields])
       SELECT EventID, EventTimestamp, EventDetails, [OtherFields]
       FROM [LinkedServerName].[RemoteDatabase].[Schema].[RemoteViewName]
       WHERE EventID > @LastCapturedEventID; -- Use timestamp if ID isn't available: EventTimestamp > (SELECT MAX(EventTimestamp) FROM dbo.LocalEventHistory)
   END
   

3. Schedule this procedure to run frequently enough to ensure that between runs, fewer than 100 new events are added. If events come in super fast, set it to run every 30 seconds or 1 minute—since you're only pulling new records each time, the overhead will be minimal.

If You Don't Have a Unique Incrementing Field #

If the view doesn't have an obvious unique key, you can still make this work by pulling all 100 records every time, then deduplicating before inserting into your local table:

CREATE PROCEDURE dbo.PullRemoteEventHistory_Deduplicated
AS
BEGIN
    SET NOCOUNT ON;

    -- Create a temp table to hold the latest 100 events from the remote view
    CREATE TABLE #TempRemoteEvents (
        EventTimestamp DATETIME,
        EventDetails NVARCHAR(MAX),
        [OtherFields] VARCHAR(50),
        -- Add all fields from the remote view here
    );

    -- Populate the temp table
    INSERT INTO #TempRemoteEvents
    SELECT EventTimestamp, EventDetails, [OtherFields]
    FROM [LinkedServerName].[RemoteDatabase].[Schema].[RemoteViewName];

    -- Insert only records that aren't already in local history
    INSERT INTO dbo.LocalEventHistory
    SELECT t.*
    FROM #TempRemoteEvents t
    LEFT JOIN dbo.LocalEventHistory l 
        ON t.EventTimestamp = l.EventTimestamp 
        AND t.EventDetails = l.EventDetails -- Use a combination of fields to uniquely identify events
    WHERE l.EventTimestamp IS NULL;

    DROP TABLE #TempRemoteEvents;
END

This is less efficient, but it guarantees you won't miss any events—as long as you run it often enough that no event gets pushed out of the 100-entry view before you've pulled it at least once.

Bonus: Ask the Admin for Change Tracking Access (If Possible) #

If you can get the remote server admin to enable Change Tracking on the underlying tables that feed the view, that's a more robust solution. Change Tracking lets you query all changes (inserts, updates) to a table since a specific point in time, regardless of what the view shows. You'd need the admin to:

1. Enable Change Tracking on the database and the relevant tables.
2. Grant you permission to query the change tracking system tables.
   If they're willing, this eliminates the need to rely on the 100-entry view limit entirely.

Final Recommendations #

• Start with the optimized scheduled procedure using unique identifiers/timestamps—it's the most reliable approach given your constraints.
• Tune the schedule frequency based on how fast events are added (test with a high frequency first, then adjust if needed).
• If you can't use unique identifiers, use the deduplication method instead.
• Only bother asking for Change Tracking if the admin is open to small configuration changes (don't push if they've already said no).

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