如何将动态列顺序的Excel数据经##temp表导入tblBase表？

核心思路 #

不要用SELECT *进行插入，显式指定目标表和临时表的列名，让数据按列名匹配而非顺序匹配，就能解决列顺序变化导致的映射错误问题。

方案一：手动指定列名（适合列固定的场景） #

如果tblBase的列是固定的，直接在INSERT和SELECT语句中明确写出对应列名，不管临时表的列顺序如何：

USE [master]
GO

-- 配置OLEDB（保持原代码不变）
EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE
GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

-- 导入Excel到临时表（保持原代码不变）
SELECT * 
INTO ##temp 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0; Database=C:\Users\NEW_01012023\Downloads\Report_SERVICES.xlsx; HDR=YES; IMEX=1',
                'SELECT * FROM [Base$]')
GO

-- 显式指定列名，按列名映射插入
INSERT INTO tblBase 
    (ID, [Entry Date], [First Name], [Last Name], [Portfolio Unit], [Demand State], [Demand Type], [Staffed Date])
SELECT 
    ID, [Entry Date], [First Name], [Last Name], [Portfolio Unit], [Demand State], [Demand Type], [Staffed Date]
FROM ##temp  

这样不管Excel里的列顺序怎么变，只要列名和tblBase一致，就能正确插入。

方案二：动态生成插入语句（适合列可能变动的场景） #

如果tblBase的列或者Excel的静态列可能有变动，可以通过系统视图动态获取tblBase的列名，自动生成匹配的INSERT语句，避免手动维护列名：

USE [master]
GO

-- 配置OLEDB（保持原代码不变）
EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE
GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

-- 导入Excel到临时表（保持原代码不变）
SELECT * 
INTO ##temp 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0; Database=C:\Users\NEW_01012023\Downloads\Report_SERVICES.xlsx; HDR=YES; IMEX=1',
                'SELECT * FROM [Base$]')
GO

-- 动态生成INSERT语句
DECLARE @columns NVARCHAR(MAX), @insertSql NVARCHAR(MAX)

-- 获取tblBase的列名，同时确保临时表中存在对应列
SELECT @columns = STRING_AGG(QUOTENAME(c.name), ', ')
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('tblBase')
AND EXISTS (SELECT 1 FROM sys.columns tc WHERE tc.object_id = OBJECT_ID('tempdb..##temp') AND tc.name = c.name)

-- 拼接插入SQL
SET @insertSql = N'INSERT INTO tblBase (' + @columns + N') SELECT ' + @columns + N' FROM ##temp'

-- 执行动态SQL
EXEC sp_executesql @insertSql

这个方案会自动匹配tblBase和##temp共有的列，自动生成插入语句，不管列顺序如何，也能兼容列的新增/删除（只要是两边共有的列）。

注意事项 #

• 确保Excel的列名和tblBase的列名完全一致（包括大小写、空格、特殊字符），否则会匹配失败。
• 如果Excel中有动态列，方案二会自动忽略这些列，只插入tblBase存在的列，符合需求。

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