创建存储过程：从临时表同步数据至4个目标表并处理关联ID

Alright, let's work through this problem together. The main roadblock you're hitting is tracking the MSP_Companies_ID for every record in your MSPTempTable after the upsert (update or insert) on MSPCompanies—you need those IDs to populate the other three tables. Let's break down two solid solutions, one using MERGE (fixed to handle bulk data) and another using separate UPDATE/INSERT statements.

解决方案1：使用MERGE + OUTPUT捕获批量ID #

Your initial MERGE attempt only stored a single ID in a variable, which fails for bulk data because variables can only hold one value at a time. Instead, use the OUTPUT clause to capture every upserted record's MSP_Companies_ID and link it back to the source INN from your temp table.

CREATE PROCEDURE UpsertMSPData
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        -- 1. Create a table variable to track INN ↔ MSP_Companies_ID mappings
        DECLARE @UpsertedCompanies TABLE (
            INN VARCHAR(20), -- Match your actual INN data type/length
            MSP_Companies_ID INT
        );

        -- 2. Perform MERGE and capture all upserted IDs
        MERGE INTO [MSPCompanies] AS [Target]
        USING [MSPTempTable] AS [Source]
            ON Target.INN = Source.INN
        WHEN MATCHED THEN
            UPDATE SET
                Target.Name = Source.Name,
                Target.ShortName = Source.ShortName,
                Target.FIO = Source.FIO,
                Target.SubjectType = Source.SubjectType,
                Target.SubjectCategory = Source.SubjectCategory
        WHEN NOT MATCHED THEN
            INSERT (INN, Name, ShortName, FIO, SubjectType, SubjectCategory)
            VALUES (Source.INN, Source.Name, Source.ShortName, Source.FIO, Source.SubjectType, Source.SubjectCategory)
        -- Output the source INN and resulting ID to our tracking table
        OUTPUT Source.INN, inserted.MSP_Companies_ID INTO @UpsertedCompanies;

        -- 3. Insert into MSPDownloads and capture new download IDs
        DECLARE @InsertedDownloads TABLE (
            INN VARCHAR(20),
            MSP_Downloads_ID INT -- Match your MSPDownloads ID data type
        );

        INSERT INTO [MSPDownloads] (INN, /* Add other required columns from MSPTempTable */)
        OUTPUT inserted.INN, inserted.MSP_Downloads_ID INTO @InsertedDownloads
        SELECT
            s.INN,
            -- Replace with actual columns from MSPTempTable for MSPDownloads
            s.SomeDownloadColumn1,
            s.SomeDownloadColumn2
        FROM [MSPTempTable] s
        JOIN @UpsertedCompanies uc ON s.INN = uc.INN;

        -- 4. Insert into MSPAccepted using captured IDs from both tables
        INSERT INTO [MSPAccepted] (MSP_Companies_ID, MSP_Downloads_ID)
        SELECT
            uc.MSP_Companies_ID,
            id.MSP_Downloads_ID
        FROM @UpsertedCompanies uc
        JOIN @InsertedDownloads id ON uc.INN = id.INN;

        -- 5. Insert into MSPAcceptedNow
        INSERT INTO [MSPAcceptedNow] (MSP_Company_ID)
        SELECT MSP_Companies_ID FROM @UpsertedCompanies;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        -- Re-throw error for debugging/logging
        THROW;
    END CATCH
END

解决方案2：使用UPDATE + INSERT捕获ID #

If you prefer to avoid MERGE, you can split the upsert into separate UPDATE and INSERT steps, capturing IDs for both operations.

CREATE PROCEDURE UpsertMSPData
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        -- 1. Create a table variable to track all INN ↔ MSP_Companies_ID mappings
        DECLARE @UpsertedCompanies TABLE (
            INN VARCHAR(20),
            MSP_Companies_ID INT
        );

        -- 2. Capture IDs for records that will be updated
        INSERT INTO @UpsertedCompanies (INN, MSP_Companies_ID)
        SELECT s.INN, t.MSP_Companies_ID
        FROM MSPTempTable s
        JOIN MSPCompanies t ON s.INN = t.INN;

        -- Perform the update
        UPDATE t
        SET
            Name = s.Name,
            ShortName = s.ShortName,
            FIO = s.FIO,
            SubjectType = s.SubjectType,
            SubjectCategory = s.SubjectCategory
        FROM MSPCompanies t
        JOIN MSPTempTable s ON s.INN = t.INN;

        -- 3. Capture IDs for newly inserted records
        INSERT INTO MSPCompanies (INN, Name, ShortName, FIO, SubjectType, SubjectCategory)
        OUTPUT inserted.INN, inserted.MSP_Companies_ID INTO @UpsertedCompanies
        SELECT
            s.INN, s.Name, s.ShortName, s.FIO, s.SubjectType, s.SubjectCategory
        FROM MSPTempTable s
        LEFT JOIN MSPCompanies t ON s.INN = t.INN
        WHERE t.INN IS NULL;

        -- 4. Insert into MSPDownloads (same as MERGE approach)
        DECLARE @InsertedDownloads TABLE (
            INN VARCHAR(20),
            MSP_Downloads_ID INT
        );

        INSERT INTO [MSPDownloads] (INN, /* Add required columns */)
        OUTPUT inserted.INN, inserted.MSP_Downloads_ID INTO @InsertedDownloads
        SELECT
            s.INN,
            s.SomeDownloadColumn1,
            s.SomeDownloadColumn2
        FROM [MSPTempTable] s
        JOIN @UpsertedCompanies uc ON s.INN = uc.INN;

        -- 5. Insert into MSPAccepted
        INSERT INTO [MSPAccepted] (MSP_Companies_ID, MSP_Downloads_ID)
        SELECT
            uc.MSP_Companies_ID,
            id.MSP_Downloads_ID
        FROM @UpsertedCompanies uc
        JOIN @InsertedDownloads id ON uc.INN = id.INN;

        -- 6. Insert into MSPAcceptedNow
        INSERT INTO [MSPAcceptedNow] (MSP_Company_ID)
        SELECT MSP_Companies_ID FROM @UpsertedCompanies;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END

关键注意事项 #

• Ensure INN is a unique key in MSPCompanies—this is critical for accurate matching between the temp table and target tables.
• Adjust column data types (like INN length) and MSPDownloads insert columns to match your actual database schema.
• The transaction wraps all operations to guarantee atomicity: either all steps succeed, or none do (prevents partial data inserts).

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