使用UNION ALL合并12个SQL表时遭遇列无效错误求助

你的代码里有两个关键问题导致报错：

• 列名不匹配：最后一个表dbo.Sep21使用的列是[Day of the week]，但其他所有表的对应列是[Day of week]（少了一个the），UNION ALL要求所有子查询的列名、数量、类型完全一致，这就是“列无效”错误的核心原因。
• INSERT语法顺序错误：你错误地在INSERT INTO前面加了SELECT * FROM Cyclistic，这不仅不符合SQL语法，而且空表的查询也没有任何作用，正确的插入逻辑应该是INSERT INTO 目标表 SELECT 数据源。

修正后的代码 #

DROP TABLE IF EXISTS Cyclistic
CREATE TABLE Cyclistic
(ride_id nvarchar(255), 
rideable_type nvarchar(255),
Ride_length timestamp,
Day_of_week float, 
start_station_name nvarchar(255),
end_station_name nvarchar(255),
member_casual nvarchar(255)
);

-- 修正语法顺序，同时统一列名
INSERT INTO Cyclistic 
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Sep22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Aug22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Jul22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Jun22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.May22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Apr22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Mar22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Feb22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Dec21
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Nov21
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Oct21
UNION ALL
-- 修正列名，去掉多余的the
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Sep21

更简洁的写法（推荐） #

可以直接用SELECT INTO一步完成表创建和数据插入，省去手动建表的步骤：

DROP TABLE IF EXISTS Cyclistic;

SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
INTO Cyclistic
FROM dbo.Sep22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Aug22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Jul22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Jun22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.May22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Apr22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Mar22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Feb22
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Dec21
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Nov21
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Oct21
UNION ALL
SELECT ride_id, rideable_type,Ride_length,[Day of week], start_station_name,end_station_name,member_casual
FROM dbo.Sep21

内容的提问来源于stack exchange，提问作者Sneh Nikkhel Sosa