SQL批量插入求助：将查询所得stdntCode批量插入StudentExam表

你的原语句存在两个核心问题：

1. 内层子查询如果返回多条stdntCode结果，会触发"子查询返回的值不止一个"的错误；
2. 后面的(4, 3,0, 1, 1, N'', '2023-01-17', '2023-02-01')写法不符合SQL语法，无法直接作为select的字段组。

正确的批量插入写法应该将查询到的stdntCode与固定值字段直接组合，让select语句返回对应行数的完整数据行，再插入目标表：

INSERT INTO StudentExam (stdntCode, ExamID, CrsID, [Degree], StudGrpID, IsAttend, Notes, dateexam, [date])
SELECT 
    stdntCode,
    4 AS ExamID,
    3 AS CrsID,
    0 AS [Degree],
    1 AS StudGrpID,
    1 AS IsAttend,
    N'' AS Notes,
    '2023-01-17' AS dateexam,
    '2023-02-01' AS [date]
FROM dbo.CourseStudent
WHERE Status = 'Active' 
  AND CrsID = 1 
  AND GrpID = 8;

如果后续需要插入多组不同的固定值（比如不同的ExamID、dateexam等），可以用VALUES子句生成固定值集合，再和CourseStudent的查询结果做交叉关联：

INSERT INTO StudentExam (stdntCode, ExamID, CrsID, [Degree], StudGrpID, IsAttend, Notes, dateexam, [date])
SELECT 
    cs.stdntCode,
    f.ExamID,
    f.CrsID,
    f.[Degree],
    f.StudGrpID,
    f.IsAttend,
    f.Notes,
    f.dateexam,
    f.[date]
FROM dbo.CourseStudent cs
CROSS JOIN (
    VALUES 
        (4, 3, 0, 1, 1, N'', '2023-01-17', '2023-02-01'),
        (5, 3, 0, 1, 1, N'', '2023-01-18', '2023-02-02') -- 第二组固定值
) AS f(ExamID, CrsID, [Degree], StudGrpID, IsAttend, Notes, dateexam, [date])
WHERE cs.Status = 'Active' 
  AND cs.CrsID = 1 
  AND cs.GrpID = 8;

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