基于Organisation表数据批量插入多表的技术实现问询

Let's tackle this problem efficiently—since you're handling bulk inserts from the Organisation table, the core challenge is capturing all newly generated UserIDs and using them to populate the related UserProductMapping and UserGroups tables without slow, loop-based operations.

Step 1: Capture Inserted UserIDs #

First, we'll use a table variable to store every UserID generated when inserting into the User table. This lets us reuse these IDs for subsequent inserts across related tables.

-- Declare a table variable to hold newly created UserIDs (adjust data type to match your schema)
DECLARE @InsertedUsers TABLE (UserID INT);

-- Insert into User table and capture all generated UserIDs
INSERT INTO [User] (userlogin, Organisationid, emailaddress, username, userpassword)
OUTPUT inserted.UserID INTO @InsertedUsers
SELECT 'AGT' + Code, organisationid, 'test@gmail.com', 'User' + Code, '123'
FROM organisation
-- Add dynamic filters here if targeting specific records
-- WHERE Organisationid = @TargetOrgID AND Code = @TargetCode;

Step 2: Bulk Insert into UserProductMapping #

Instead of writing repetitive individual INSERT statements, we use a CROSS JOIN with a value list to generate all required product mappings for every new user in one batch.

-- Insert multiple product associations for each new user
INSERT INTO UserProductMapping (UserID, ProductID)
SELECT iu.UserID, p.ProductID
FROM @InsertedUsers iu
CROSS JOIN (
    -- List all required ProductIDs here
    VALUES ('11'), ('22'), ('33'), ('44'), ('55')
) p(ProductID);

Step 3: Bulk Insert into UserGroups #

We use the same CROSS JOIN approach to assign the required groups to each newly created user:

-- Assign multiple groups to each new user
INSERT INTO UserGroups (UserID, GroupID)
SELECT iu.UserID, g.GroupID
FROM @InsertedUsers iu
CROSS JOIN (
    -- List all required GroupIDs here
    VALUES (1), (3)
) g(GroupID);

Key Notes #

• Efficiency: This approach avoids looping through each Organisation record individually—bulk operations are always faster for this type of work.
• Dynamic Parameters: If you need to target specific Organisationid or Code values, uncomment and adjust the WHERE clause in the initial INSERT into [User] (you can pass these as parameters like @TargetOrgID).
• Keyword Handling: User is a reserved SQL keyword, so we wrap it in square brackets ([User]) to prevent syntax errors.

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