新手求助：创建从多表同步数据至SummaryTable的存储过程

Alright, I see you're new to this and need a hand building a stored procedure to sync specific fields from multiple tables into your existing SummaryTable. Let's put this together step by step.

First, Let's Confirm the Field Mapping #

Here's the clear breakdown of which source field maps to your SummaryTable columns:

• BillNo → TxBillItem.BillNo
• AccNo → TxBillItem.AccNo
• TransactionDate → TxBill.TxDate
• Station → Component.referenceCode
• Amount → TxBillItem.Amount
• PaymentId → TxBillPayment.PaymentTypeId
• ReceiptNo → TxBillPayment.ReferenceNo
• Status → TxBill.status
• Note: You mentioned "VOT..." which seems cut off—if there's an additional field to sync, just add it to the query following the same pattern.

The Stored Procedure Code #

I've included two common sync scenarios: inserting only new records, and upserting (inserting new + updating existing). Pick the one that fits your needs, and adjust join conditions as needed (I assumed tables link via BillNo, but double-check your actual relationships!).

CREATE PROCEDURE SyncToSummaryTable
AS
BEGIN
    -- Prevent extra result sets from interfering with the procedure
    SET NOCOUNT ON;

    -- ------------------------------
    -- Option 1: Insert only new records
    -- Uncomment this block if you don't need to update existing data
    -- ------------------------------
    /*
    INSERT INTO SummaryTable (BillNo, AccNo, TransactionDate, Station, Amount, PaymentId, ReceiptNo, Status)
    SELECT 
        tbi.BillNo,
        tbi.AccNo,
        tb.TxDate AS TransactionDate,
        c.referenceCode AS Station,
        tbi.Amount,
        tbp.PaymentTypeId AS PaymentId,
        tbp.ReferenceNo AS ReceiptNo,
        tb.status AS Status
    FROM TxBillItem tbi
    -- Join to TxBill to get date and status
    INNER JOIN TxBill tb ON tbi.BillNo = tb.BillNo
    -- Join to Component (adjust the join key if it's not BillNo!)
    INNER JOIN Component c ON tbi.BillNo = c.BillNo
    -- Join to TxBillPayment (adjust join key if needed)
    INNER JOIN TxBillPayment tbp ON tb.BillNo = tbp.BillNo
    -- Avoid duplicates by checking if the record already exists
    WHERE NOT EXISTS (
        SELECT 1 FROM SummaryTable st WHERE st.BillNo = tbi.BillNo
    );
    */

    -- ------------------------------
    -- Option 2: Upsert (Insert new + Update existing)
    -- Use this if you need to refresh existing records and add new ones
    -- ------------------------------
    MERGE INTO SummaryTable st
    USING (
        SELECT 
            tbi.BillNo,
            tbi.AccNo,
            tb.TxDate AS TransactionDate,
            c.referenceCode AS Station,
            tbi.Amount,
            tbp.PaymentTypeId AS PaymentId,
            tbp.ReferenceNo AS ReceiptNo,
            tb.status AS Status
        FROM TxBillItem tbi
        INNER JOIN TxBill tb ON tbi.BillNo = tb.BillNo
        INNER JOIN Component c ON tbi.BillNo = c.BillNo -- Confirm this join key!
        INNER JOIN TxBillPayment tbp ON tb.BillNo = tbp.BillNo
    ) AS SourceData
    ON st.BillNo = SourceData.BillNo -- Assume BillNo is the unique identifier for matching
    WHEN MATCHED THEN
        UPDATE SET
            st.AccNo = SourceData.AccNo,
            st.TransactionDate = SourceData.TransactionDate,
            st.Station = SourceData.Station,
            st.Amount = SourceData.Amount,
            st.PaymentId = SourceData.PaymentId,
            st.ReceiptNo = SourceData.ReceiptNo,
            st.Status = SourceData.Status
    WHEN NOT MATCHED THEN
        INSERT (BillNo, AccNo, TransactionDate, Station, Amount, PaymentId, ReceiptNo, Status)
        VALUES (SourceData.BillNo, SourceData.AccNo, SourceData.TransactionDate, SourceData.Station, SourceData.Amount, SourceData.PaymentId, SourceData.ReceiptNo, SourceData.Status);

    -- Optional: Confirm completion
    PRINT 'Data sync finished successfully.';
END
GO

Key Notes to Keep in Mind #

• Join Conditions: I guessed tables link via BillNo, but this might not be right for Component (maybe it uses AccNo instead?). Double-check your table relationships to avoid missing or incorrect data.
• Permissions: Make sure the user running this procedure has SELECT access to all source tables, and INSERT/UPDATE access to SummaryTable.
• Missing Field: If you finish that "VOT..." field, just add it to the source query's SELECT list and include it in the INSERT/UPDATE sections.

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