将多个Excel工作表数据导入单个SQL Server表的解决方案咨询

以下方案均无需SSIS支持，仅需数据库引擎读写权限即可实现需求。

方案1：使用OPENROWSET直接查询Excel并批量插入 #

该方案需要服务器已安装对应版本的ACE驱动，且你拥有修改服务器配置的权限，无权限可直接跳过该方案

• 第一步：提前在数据库中创建结构和Excel列完全匹配的目标表，确认列名、数据类型对应无误
• 第二步：开启Ad Hoc分布式查询配置

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

• 第三步：对每个工作表执行插入语句，替换对应参数即可

INSERT INTO 目标表名 (列1, 列2, 列3, 列N)
SELECT 列1, 列2, 列3, 列N
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=你的Excel文件绝对路径.xlsx',
    'SELECT * FROM [对应工作表名$]'
);

• 所有工作表导入完成后，可还原服务器配置：

sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
sp_configure 'show advanced options', 0;
RECONFIGURE;
GO

• 若所有工作表结构完全一致，可编写动态SQL批量遍历所有工作表，无需手动编写54次插入语句

方案2：自定义SQL Server导入向导映射（无额外权限要求） #

你之前使用导入向导生成多表是因为默认按工作表生成目标表，修改配置即可实现导入同一表：

• 提前创建好结构匹配的目标表
• 打开导入向导，选择Excel数据源，选中第一个Excel文件
• 选择你的SQL Server数据库为目标，到「指定表复制或查询」步骤时，选择「编写查询以指定要传输的数据」
• 手动编写SQL查询对应工作表的列，下一步后将源列映射到提前创建好的目标表的对应列，执行导入
• 重复以上操作依次导入剩余53个工作表即可
• 嫌重复操作麻烦可以先在本地合并所有工作表到单个Excel的同一工作表：打开Excel -> 数据选项卡 -> 获取数据 -> 从文件选择所有待导入Excel -> 选中全部工作表，用Power Query合并为单表后一次性导入

方案3：CSV批量导入（适合有命令行访问权限场景） #

• 先在Excel中批量导出所有工作表为CSV格式文件
• 用bcp命令批量导入CSV到同一目标表，示例命令如下：

bcp 数据库名.dbo.目标表名 in 对应CSV文件路径.csv -S SQL实例地址 -U 用户名 -P 密码 -c -t, -r\n

• 编写简单批处理遍历所有CSV文件执行该命令，即可完成批量导入

提示：所有方案执行前建议先导入1个工作表的少量数据做测试，验证列映射、数据类型转换无问题后再批量导入全量数据，避免错误数据污染目标表。

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