如何在单个存储过程中批量添加父表Rooms与子表RoomPhotoUrls记录？

Got it, let's walk through how to make this work—your approach using table-valued parameters (TVPs) for batch inserts is totally on the right track, it’s just a matter of linking the parent and child records correctly since the parent’s ID gets generated after insertion. Here’s a step-by-step solution tailored to your scenario:

First, Create the Table-Valued Parameter Types #

Before writing the stored procedure, you need to define TVP types that match the structure of your Rooms and RoomPhotoUrls tables. The key here is adding a temporary identifier (I’ll call it BatchRoomKey) to both TVPs—this lets us map which child records belong to which parent, even before the parent’s real RoomId is generated.

-- Create TVP type for Rooms (parent table)
CREATE TYPE RoomTVP AS TABLE (
    BatchRoomKey INT, -- Temporary unique key to link parent to child records
    RoomName NVARCHAR(100),
    RoomType NVARCHAR(50),
    -- Add all other columns from your Rooms table except the auto-incrementing RoomId
);

-- Create TVP type for RoomPhotoUrls (child table)
CREATE TYPE RoomPhotoTVP AS TABLE (
    BatchRoomKey INT, -- Matches the parent's BatchRoomKey to associate records
    PhotoUrl NVARCHAR(255),
    IsPrimary BIT,
    -- Add all other columns from your RoomPhotoUrls table except RoomId
);

Build the Stored Procedure #

This procedure will first insert all parent records, capture the generated RoomId values, then use those IDs to insert the corresponding child records. We’ll also add a transaction to ensure everything succeeds or fails together—critical for maintaining data integrity.

CREATE PROCEDURE BatchInsertRoomsAndPhotos
    @Rooms RoomTVP READONLY,
    @RoomPhotos RoomPhotoTVP READONLY
AS
BEGIN
    SET NOCOUNT ON;

    -- Table variable to store the mapping between temporary BatchRoomKey and real RoomId
    DECLARE @InsertedRooms TABLE (
        RoomId INT,
        BatchRoomKey INT
    );

    BEGIN TRANSACTION;
    BEGIN TRY
        -- Insert parent records into Rooms, and capture the generated RoomId + BatchRoomKey
        INSERT INTO Rooms (RoomName, RoomType) -- Replace with your actual Rooms columns
        OUTPUT inserted.RoomId, inserted.BatchRoomKey INTO @InsertedRooms
        SELECT RoomName, RoomType -- Match the columns from the TVP (exclude RoomId)
        FROM @Rooms;

        -- Insert child records into RoomPhotoUrls, using the mapped RoomId
        INSERT INTO RoomPhotoUrls (RoomId, PhotoUrl, IsPrimary) -- Replace with your actual columns
        SELECT ir.RoomId, rp.PhotoUrl, rp.IsPrimary
        FROM @RoomPhotos rp
        INNER JOIN @InsertedRooms ir 
            ON rp.BatchRoomKey = ir.BatchRoomKey;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW; -- Re-throw the error so the caller knows the batch failed
    END CATCH
END

Key Details to Note #

• BatchRoomKey: When you prepare the data to send to the stored procedure, assign a unique BatchRoomKey to each parent record (e.g., 1, 2, 3...), and use the same key for all child records belonging to that parent. This is how we link the temporary input data to the generated database IDs.
• Auto-Incrementing RoomId: This assumes your Rooms table uses an auto-incrementing IDENTITY column for RoomId—if you’re using a GUID or manually assigned key, you can skip the BatchRoomKey and just pass the pre-generated key in the TVP.
• Transaction Safety: The TRY/CATCH block with transaction ensures that if any part of the batch fails, none of the records are inserted—no half-completed parent/child pairs left in your database.

How to Call the Procedure #

When calling from your application, you’d populate the TVPs with your parent and child data (making sure BatchRoomKey matches across related records), then execute the procedure with those TVPs as parameters.

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