SQL Server：如何创建根据Request表批量插入Device表的存储过程

Got it, let's tackle this problem. We need a stored procedure that reads the noOfdevice value from the Request table and inserts the corresponding number of rows into the Device table, with properly formatted device IDs (like dev5, dev6, etc.) for the requesting user.

Step-by-Step Stored Procedure (SQL Server Example) #

This procedure targets a specific request by its ID, but we'll also cover how to adjust it for all requests later.

CREATE PROCEDURE InsertDevicesFromRequest
    @RequestID INT
AS
BEGIN
    SET NOCOUNT ON; -- Prevents extra result sets from interfering with output

    -- Declare variables to store request details and device ID sequence info
    DECLARE @noOfDevice INT;
    DECLARE @requestedBy VARCHAR(50);
    DECLARE @maxExistingDeviceNum INT;
    DECLARE @startingDeviceNum INT;

    -- Fetch the request's device count and requester name
    SELECT 
        @noOfDevice = noOfdevice, 
        @requestedBy = requestedby
    FROM Request
    WHERE ID = @RequestID;

    -- Check if the request exists
    IF @noOfDevice IS NULL
    BEGIN
        PRINT 'Error: Request with ID ' + CAST(@RequestID AS VARCHAR) + ' not found.';
        RETURN; -- Exit procedure if no matching request
    END;

    -- Get the highest existing device number for the requester (e.g., 4 from 'dev4')
    SELECT 
        @maxExistingDeviceNum = CAST(RIGHT(ID, LEN(ID)-3) AS INT)
    FROM Device
    WHERE requestedby = @requestedBy
    ORDER BY CAST(RIGHT(ID, LEN(ID)-3) AS INT) DESC
    OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY; -- Grab the top 1 highest number

    -- Set starting number: if no devices exist, start at 1; else start at next number
    SET @startingDeviceNum = ISNULL(@maxExistingDeviceNum, 0) + 1;

    -- Generate the required number of device IDs using a recursive CTE
    WITH DeviceNumberSequence AS (
        SELECT @startingDeviceNum AS DeviceNum
        UNION ALL
        SELECT DeviceNum + 1
        FROM DeviceNumberSequence
        WHERE DeviceNum < @startingDeviceNum + @noOfDevice - 1
    )
    INSERT INTO Device (ID, requestedby)
    SELECT 
        'dev' + CAST(DeviceNum AS VARCHAR), -- Format ID as 'devX'
        @requestedBy
    FROM DeviceNumberSequence
    OPTION (MAXRECURSION 0); -- Allow unlimited recursion for large device counts

    -- Confirm success
    PRINT CAST(@noOfDevice AS VARCHAR) + ' new devices inserted for user ' + @requestedBy;
END;

How to Use the Procedure #

To run the procedure for the request with ID 1 (from your example):

EXEC InsertDevicesFromRequest @RequestID = 1;

This will insert 4 new rows into the Device table (since noOfdevice is 4) with IDs dev5, dev6, dev7, dev8 for user sam.

Adjusting to Process All Requests #

If you want to process every row in the Request table at once, modify the procedure to loop through all requests:

CREATE PROCEDURE InsertDevicesFromAllRequests
AS
BEGIN
    SET NOCOUNT ON;

    -- Declare cursor to iterate through all requests
    DECLARE @RequestID INT;
    DECLARE request_cursor CURSOR FOR
        SELECT ID FROM Request;

    OPEN request_cursor;
    FETCH NEXT FROM request_cursor INTO @RequestID;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Call the single-request procedure for each entry
        EXEC InsertDevicesFromRequest @RequestID = @RequestID;
        FETCH NEXT FROM request_cursor INTO @RequestID;
    END;

    CLOSE request_cursor;
    DEALLOCATE request_cursor;
END;

Run this with:

EXEC InsertDevicesFromAllRequests;

Notes #

• This assumes your Device ID format is always dev followed by a number. If your ID format changes, adjust the RIGHT(ID, LEN(ID)-3) part to match.
• For MySQL, the syntax for stored procedures and recursive CTEs differs slightly (MySQL uses the RECURSIVE keyword in CTEs and different cursor syntax), but the core logic remains the same.

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