如何使用INSERT结合SELECT语句向stuattrecordAMPM表插入数据

查询结果示例 #

原查询语句 #

SELECT DISTINCT 
    COALESCE(tt.ID, t1.ID) AS ID,
    COALESCE(tt.Name, t1.Name) AS Name,
    COALESCE(tt.Class, t1.Class) AS Class,
    tt.Date, tt.Intime, tt.Outtime, tt.INAM, tt.OUTPM   
FROM 
    stuattrecordAMPM AS t1
CROSS JOIN 
    (SELECT * FROM stuattrecordAMPM  
     UNION ALL
     SELECT 
         NULL, NULL, NULL, Date, Holiday_Name, Holiday_Name,
         Status, Status 
     FROM HolidayList) AS tt 
ORDER BY
    [ID]

插入数据的实现方法 #

直接使用INSERT INTO ... SELECT ...语法，将查询结果插入目标表即可。建议明确指定列名以避免表结构变更引发错误，同时可添加过滤条件防止重复插入：

INSERT INTO stuattrecordAMPM (ID, Name, Class, Date, Intime, Outtime, INAM, OUTPM)
SELECT DISTINCT 
    COALESCE(tt.ID, t1.ID) AS ID,
    COALESCE(tt.Name, t1.Name) AS Name,
    COALESCE(tt.Class, t1.Class) AS Class,
    tt.Date, tt.Intime, tt.Outtime, tt.INAM, tt.OUTPM   
FROM 
    stuattrecordAMPM AS t1
CROSS JOIN 
    (SELECT * FROM stuattrecordAMPM  
     UNION ALL
     SELECT 
         NULL, NULL, NULL, Date, Holiday_Name, Holiday_Name,
         Status, Status 
     FROM HolidayList) AS tt
-- 可选：过滤已存在的记录，避免重复插入（以ID和Date作为唯一标识为例）
WHERE NOT EXISTS (
    SELECT 1 
    FROM stuattrecordAMPM s 
    WHERE s.ID = COALESCE(tt.ID, t1.ID) 
      AND s.Date = tt.Date
)

注意事项 #

• 确保INSERT INTO后指定的列顺序与SELECT返回的列顺序、数据类型完全匹配
• 若目标表有主键或唯一约束，必须避免插入重复数据，否则会触发报错，上述WHERE NOT EXISTS是常用的防重复手段
• 原查询中的ORDER BY在插入语句中可省略，因为数据库存储数据的顺序不影响后续查询，除非有特定业务需要

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