如何使用SCOPE_IDENTITY()同时向两表插入数据并关联自动生成ID？

你的问题核心在于批量插入时，SCOPE_IDENTITY()只能返回最后一行的标识值，无法对应每一条插入的camera记录与cameraLense记录的关联关系，同时原代码里还有两个不必要的错误操作：显式指定标识列cameraId（无需手动插入）、多余的延迟等待。

下面是修正后的完整实现方案：

步骤1：创建临时存储保存关联关系 #

先定义一个表变量，用来存储每个新生成的cameraId，以及它对应的源表PRODUCT中的CAMERA_ID（后续用来匹配cameraLense数据）：

DECLARE @InsertedCameras TABLE (
    cameraId INT, -- 类型需与dbo.camera.cameraId完全一致
    sourceCameraId INT -- 对应源表[SQL_CANADA].[BASE].[dbo].PRODUCT的CAMERA_ID
)

步骤2：插入camera表并捕获所有新生成的标识值 #

移除手动指定的cameraId列（标识列由数据库自动生成），通过OUTPUT子句将新生成的cameraId和源表的CAMERA_ID存入临时变量：

INSERT INTO dbo.camera
(
    productName,
    productDesc,
    productCode
)
OUTPUT inserted.cameraId, s.CAMERA_ID INTO @InsertedCameras
SELECT 
    PRODUCT_NAME,
    PRODUCT_DESCRIPTION,
    PRODUCT_CODE
FROM [SQL_CANADA].[BASE].[dbo].PRODUCT s
WHERE ORIGIN_ID = 19327761

步骤3：关联临时数据插入cameraLense表 #

通过源表的CAMERA_ID与临时变量中的记录关联，确保每条cameraLense都匹配到正确的cameraId：

INSERT INTO dbo.cameraLense
(
    lenseId,
    cameraId,
    type,
    materialId,
    isCurrentYear,
    modelNumber
)
SELECT 
    p.LENSE_ID,
    ic.cameraId,
    p.LENSE_TYPE,
    p.MATERIAL_ID,
    p.IS_CURRENT_YEAR,
    p.MODEL_NUMBER
FROM [SQL_CANADA].[BASE].[dbo].PRODUCT p
JOIN @InsertedCameras ic ON p.CAMERA_ID = ic.sourceCameraId
WHERE p.ORIGIN_ID = 19327761

关键说明 #

1. SCOPE_IDENTITY()的局限性：它仅返回当前作用域内最后一次插入操作生成的标识值，批量插入100行时，只能拿到最后一行的cameraId，无法实现一对一的关联匹配。
2. 标识列的正确使用：cameraId是自动生成的标识列，显式指定该列插入会触发语法错误（除非开启IDENTITY_INSERT，这是特殊场景才用的操作），直接省略即可。
3. 移除不必要的延迟：SQL语句按顺序执行，第一个INSERT完成后才会执行第二个，延迟等待完全多余，还会浪费资源。

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