SQL报错：列名或提供值数量与表定义不匹配，请求排查

你遇到的报错Column name or number of supplied values does not match table definition，核心原因及对应解决方案如下：

1. 临时表已存在且结构不匹配 #

如果之前运行过这段代码但未清理临时表，部分SQL Server版本中Create Table语句会因表已存在执行失败，后续Insert操作会沿用旧的临时表结构，导致列数/列定义不匹配。

修正方法：创建临时表前先删除已存在的同名表：

Drop Table if exists #PercentPopulationVaccinated
Create Table #PercentPopulationVaccinated
(
continent nvarchar(255),
Location nvarchar(255),
Date datetime,
Population numeric,
New_vaccinations numeric,
RollingPeopleVaccinated numeric
)

2. 插入时未指定列列表（潜在风险） #

虽然当前Select语句列数与临时表一致，但如果后续临时表结构调整（比如列顺序变更），Insert会直接出错。更稳妥的方式是明确指定插入的列名，避免依赖顺序匹配：

修正后的Insert语句：

Insert into #PercentPopulationVaccinated
(continent, Location, Date, Population, New_vaccinations, RollingPeopleVaccinated)
select 
    dea.continent, 
    dea.location, 
    dea.date, 
    dea.population, 
    vac.new_vaccinations,
    sum(convert(bigint,vac.new_vaccinations)) OVER (Partition by dea.Location order by dea.location, dea.date) as RollingPeopleVaccinated
from PortfolioProject..CovidDeaths dea
join PortfolioProject..CovidVaccinations vac 
    on dea.location = vac.location
    and dea.date = vac.date
where dea.continent is not null

额外验证点 #

• 检查CovidVaccinations表的new_vaccinations列是否为可转换为bigint的类型（比如varchar类型的非数字值会导致转换失败，需提前排查）
• 确认CovidDeaths表的population列是numeric类型，与临时表定义一致

内容的提问来源于stack exchange，提问作者Radek.S.