创建Menu与Menu_Temp表数据同步存储过程的技术问询

Alright, let's build that sync stored procedure you need. The core goal is to mirror Menu_Temp data to Menu with your specified rules: insert rows missing from Menu, and update existing rows only when Menu.DateReg is newer than Menu_Temp.DateReg.

Full Stored Procedure Code #

CREATE PROCEDURE dbo.SyncMenuFromTemp
AS
BEGIN
    SET NOCOUNT ON; -- Avoid extra result sets that can break app integrations

    -- Use MERGE for efficient combined insert/update logic
    MERGE INTO dbo.Menu AS Target
    USING dbo.Menu_Temp AS Source
        -- Match rows using your unique identifier (assuming Date + Ref are the composite key)
        ON Target.[Date] = Source.[Date] 
        AND Target.[Ref] = Source.[Ref]
    -- Update existing rows only if Menu's DateReg is newer than Temp's
    WHEN MATCHED AND Target.DateReg > Source.DateReg THEN
        UPDATE SET
            Target.[Art] = Source.[Art],
            Target.[Dish] = Source.[Dish],
            Target.DateReg = Source.DateReg,
            Target.[Zone] = Source.[Zone]
    -- Insert rows that exist in Temp but not in Menu
    WHEN NOT MATCHED BY Target THEN
        INSERT ([Date], [Ref], [Art], [Dish], DateReg, [Zone])
        VALUES (Source.[Date], Source.[Ref], Source.[Art], Source.[Dish], Source.DateReg, Source.[Zone]);

    -- Optional: Return stats for debugging/monitoring
    SELECT 
        @@ROWCOUNT AS TotalRowsAffected,
        (SELECT COUNT(*) FROM dbo.Menu_Temp) - (SELECT COUNT(*) FROM dbo.Menu) AS RowsInserted,
        (SELECT COUNT(*) 
         FROM dbo.Menu m 
         JOIN dbo.Menu_Temp mt ON m.[Date] = mt.[Date] AND m.[Ref] = mt.[Ref] 
         WHERE m.DateReg > mt.DateReg) AS RowsUpdated;
END
GO

Important Details: #

• Unique Key Assumption: I used [Date] and [Ref] as the matching columns because they're non-null and the most logical unique identifiers for menu items. If your actual primary/unique key is different (e.g., a single ID column), adjust the ON clause to match your schema.
• MERGE vs Separate Queries: MERGE is better here because it processes both operations in one pass over the tables, which is faster and reduces lock contention compared to running separate INSERT and UPDATE statements.
• Unusual Update Rule: Your requirement to update when Menu.DateReg is newer than Menu_Temp.DateReg is a bit out of the ordinary — typically, you'd update if the temp table has a newer (higher) DateReg (swap to Source.DateReg > Target.DateReg if that's what you intended).
• Your Original Diff Code: The query you provided finds rows in Menu that aren't in Menu_Temp, which isn't needed for this sync flow (we care about rows in Menu_Temp that need to be added or updated in Menu). The MERGE handles all necessary difference checks internally.

To Run the Procedure #

Just execute this command:

EXEC dbo.SyncMenuFromTemp;

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