多Schema环境下遍历Company表记录批量插入Menu表的SQL问询

嘿，这个问题其实很好解决——你现在的核心需求是把「遍历Schema」和「每个Schema下Company表的记录」这两层逻辑结合起来，不用单独去遍历Company的结果集，直接用动态SQL结合批量插入或者嵌套遍历就能搞定。我给你两种常用的方案，看你需求选：

方案一：批量插入（推荐，效率更高） #

如果你的Menu表插入逻辑比较统一（比如每条记录的其他字段都是固定值或从Company表取），完全不用逐行遍历Company的结果集，直接用INSERT...SELECT的方式一次性批量插入，代码更简洁效率也更高：

-- 定义临时表存储所有包含Company表的Schema名称
DECLARE @SchemaList TABLE (SchemaName NVARCHAR(128))

-- 从系统视图筛选出有Company表的Schema
INSERT INTO @SchemaList (SchemaName)
SELECT s.name
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
WHERE t.name = 'Company'

-- 定义游标遍历每个Schema
DECLARE @CurrentSchema NVARCHAR(128)
DECLARE SchemaCursor CURSOR FOR
SELECT SchemaName FROM @SchemaList

OPEN SchemaCursor
FETCH NEXT FROM SchemaCursor INTO @CurrentSchema

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 生成动态SQL：从当前Schema的Company表取Id，批量插入Menu表
    DECLARE @Sql NVARCHAR(MAX) = 
    N'INSERT INTO [' + @CurrentSchema + N'].Menu (CompanyId, MenuName, SortOrder)
      SELECT 
          Id, 
          ''系统默认菜单'', -- 这里替换成你的固定值或其他字段值
          1 -- 排序字段示例
      FROM [' + @CurrentSchema + N'].Company'

    -- 执行动态SQL
    EXEC sp_executesql @Sql

    FETCH NEXT FROM SchemaCursor INTO @CurrentSchema
END

CLOSE SchemaCursor
DEALLOCATE SchemaCursor

这段代码的逻辑： #

1. 先通过sys.schemas和sys.tables系统视图，自动找出所有包含Company表的Schema，避免手动维护Schema列表
2. 用游标逐个处理每个Schema
3. 核心是在动态SQL里用INSERT...SELECT，直接把当前Schema下所有Company的记录对应插入Menu表，省去了单独遍历Company结果集的步骤

方案二：逐行遍历Company记录（适合特殊业务逻辑） #

如果每条Menu记录需要单独处理（比如不同的Company对应不同的菜单名称或其他自定义逻辑），可以先收集所有Schema+CompanyId的组合，再逐个遍历生成INSERT语句：

-- 定义临时表存储所有需要处理的Schema和CompanyId
DECLARE @AllCompanyRecords TABLE (SchemaName NVARCHAR(128), CompanyId INT)

-- 收集所有符合条件的Schema和CompanyId
INSERT INTO @AllCompanyRecords (SchemaName, CompanyId)
SELECT 
    s.name, 
    c.Id
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
-- 关联到具体的Company表，确保存在Id列
JOIN sys.columns col ON t.object_id = col.object_id
CROSS APPLY (
    SELECT Id FROM [' + s.name + N'].Company
) c
WHERE t.name = 'Company' AND col.name = 'Id'
-- 这里可以加过滤条件，比如只处理特定状态的Company
-- AND c.IsActive = 1

-- 遍历每条记录生成INSERT
DECLARE @Schema NVARCHAR(128), @Id INT
DECLARE RecordCursor CURSOR FOR
SELECT SchemaName, CompanyId FROM @AllCompanyRecords

OPEN RecordCursor
FETCH NEXT FROM RecordCursor INTO @Schema, @Id

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 生成针对单条Company的INSERT语句，这里可以加入自定义逻辑
    DECLARE @MenuName NVARCHAR(50) = N'菜单_' + CAST(@Id AS NVARCHAR(10))
    DECLARE @Sql NVARCHAR(MAX) = 
    N'INSERT INTO [' + @Schema + N'].Menu (CompanyId, MenuName)
      VALUES (' + CAST(@Id AS NVARCHAR(10)) + N', N''' + @MenuName + N''')'

    EXEC sp_executesql @Sql

    FETCH NEXT FROM RecordCursor INTO @Schema, @Id
END

CLOSE RecordCursor
DEALLOCATE RecordCursor

注意事项： #

• 确保所有Schema下的Menu表结构一致，否则动态SQL会执行失败
• 推荐用sp_executesql执行动态SQL，比直接用EXEC更安全，能降低SQL注入风险
• 如果数据量较大，建议加上事务处理，避免部分插入成功部分失败的情况

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