如何将SQL交叉连接查询结果插入至数据库的table1表中？

现有数据库表结构 #

studentdata表 #

HolidayList表 #

原交叉连接查询SQL #

SELECT
    studentdata.ID, studentdata.Name, studentdata.Class,
    HolidayList.Date AS Date, HolidayList.Holiday_Name AS Intime,
    Holiday_Name AS Outtime, HolidayList.Status AS INAM,
    HolidayList.Status AS OUTPM 
FROM
    studentdata 
CROSS JOIN 
    HolidayList 

该查询输出结果如下：

插入查询结果到table1的方法 #

直接使用INSERT INTO ... SELECT ...语法即可实现，前提是table1的字段数量、数据类型与查询结果完全匹配。完整SQL语句如下：

INSERT INTO table1 (ID, Name, Class, Date, Intime, Outtime, INAM, OUTPM)
SELECT
    studentdata.ID, studentdata.Name, studentdata.Class,
    HolidayList.Date AS Date, HolidayList.Holiday_Name AS Intime,
    Holiday_Name AS Outtime, HolidayList.Status AS INAM,
    HolidayList.Status AS OUTPM 
FROM
    studentdata 
CROSS JOIN 
    HolidayList 

补充说明 #

• 若table1的字段顺序和查询结果的字段顺序完全一致，可省略INSERT INTO后的字段列表，但显式指定字段更稳妥，能避免因表结构变更导致的插入错误。
• 需确保table1已在数据库中创建，且每个字段的数据类型与查询结果对应字段兼容。

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