首列为列名的Excel导入SQL Server 2014的最优方法咨询

刚好之前处理过一模一样的场景，给你几个实用的方案，你根据自己的技术栈和需求选最顺手的：

这是最省心的方法，先把Excel结构转成常规格式再导入：

• 打开你的Excel文件，选中所有数据区域（包括首列的列名和后面的列数据）
• 右键选中区域，选择「转置」，此时原来的列会变成行，首列的列名就自动变成了表头
• 转置后记得检查第一行是否是正确的列名，然后保存Excel
• 打开SQL Server Management Studio（SSMS），右键目标数据库→「任务」→「导入数据」，启动导入导出向导
• 数据源选择「Microsoft Excel」，浏览选中你的Excel文件，根据Excel版本选择对应格式（比如Excel 2016），勾选「第一行包含列名」
• 目标选择「SQL Server Native Client 11.0」，填写你的SQL Server 2014服务器名、认证方式、目标数据库
• 选择「复制一个或多个表或视图的数据」，选中转置后的Excel工作表
• 后续步骤可以调整目标表名、数据类型映射（比如把文本转成合适的SQL类型），最后点击「完成」执行导入即可

如果以后需要定期导入这类Excel，SSIS是最优解：

• 打开SQL Server Data Tools（SSDT，要对应SQL Server 2014的版本），新建Integration Services项目
• 拖一个「Excel源」组件到设计界面，配置数据源：选择你的Excel文件，勾选「第一行包含列名」，此时Excel源的输出列是原来的列（比如Column1对应首列的SQL列名，Column2、Column3对应数据列）
• 拖一个「转置」组件，连接Excel源的输出。在转置组件的编辑器里，设置「透视键列」为Column1（也就是存储SQL列名的列），「透视值列」为所有数据列（比如Column2、Column3...），这样转置后就会生成正确的表结构
• 拖一个「OLE DB目标」组件，连接到你的SQL Server 2014数据库，选择目标表（可以新建表，SSIS会自动根据转置后的结构创建）
• 配置列映射，确保转置后的列和SQL表的列一一对应
• 运行包测试，没问题的话可以部署到SQL Server代理，设置定时任务自动执行

如果习惯用SQL脚本处理，可以用OPENROWSET读取Excel再转置：
首先要确保SQL Server启用了Ad Hoc Distributed Queries（如果没开的话先执行下面的脚本）：

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

然后用动态SQL来处理不确定的列数和行数（比静态PIVOT更灵活）：

DECLARE @ExcelPath NVARCHAR(1000) = 'C:\YourExcelFile.xlsx';
DECLARE @SheetName NVARCHAR(100) = '[Sheet1$]';
DECLARE @PivotColumns NVARCHAR(MAX);
DECLARE @SelectColumns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

-- 第一步：获取所有SQL列名（Excel首列的值）
WITH ColumnNames AS (
    SELECT [Column1] AS ColName
    FROM OPENROWSET(
        'Microsoft.ACE.OLEDB.12.0',
        'Excel 12.0 Xml;HDR=NO;Database=' + @ExcelPath,
        'SELECT * FROM ' + @SheetName
    )
)
SELECT @PivotColumns = STRING_AGG(QUOTENAME(ColName), ', ') FROM ColumnNames;

-- 第二步：生成转置后的选择列
WITH DataRows AS (
    SELECT COLUMN_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = (SELECT TOP 1 TABLE_NAME 
                        FROM OPENROWSET(
                            'Microsoft.ACE.OLEDB.12.0',
                            'Excel 12.0 Xml;HDR=NO;Database=' + @ExcelPath,
                            'SELECT * FROM ' + @SheetName
                        ) AS t
                        CROSS APPLY sys.dm_exec_describe_first_result_set(N'SELECT * FROM ' + @SheetName, NULL, 0) AS cols)
    AND COLUMN_NAME <> 'Column1'
)
SELECT @SelectColumns = STRING_AGG(
    'MAX(CASE WHEN ColName = ' + QUOTENAME(ColName, '''') + ' THEN ' + COLUMN_NAME + ' END) AS ' + QUOTENAME(ColName),
    ', ' + CHAR(13) + CHAR(10)
)
FROM DataRows
CROSS JOIN ColumnNames;

-- 第三步：生成最终的转置SQL
SET @SQL = N'
WITH ExcelData AS (
    SELECT [Column1] AS ColName, ' + STUFF((SELECT ', ' + COLUMN_NAME 
                                            FROM DataRows
                                            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + '
    FROM OPENROWSET(
        ''Microsoft.ACE.OLEDB.12.0'',
        ''Excel 12.0 Xml;HDR=NO;Database=' + @ExcelPath + ''',
        ''SELECT * FROM ' + @SheetName + '''
    )
)
SELECT ' + @SelectColumns + '
FROM ExcelData
GROUP BY (SELECT NULL);';

-- 执行SQL导入到目标表（这里可以用INSERT INTO或者SELECT INTO）
EXEC sp_executesql @SQL;

注意：如果你的Excel是旧版本（.xls），把Excel 12.0 Xml改成Excel 8.0。

Excel自带的Power Query处理这种结构非常灵活：

• 打开你的Excel文件，选中数据区域，点击「数据」选项卡→「从表格/区域」（旧版Excel是「获取和转换数据」组里的「自表格」）
• 在Power Query编辑器里，选中首列（存储SQL列名的列），点击「转换」选项卡→「转置」
• 转置后，第一行就是原来的首列值，点击「转换」→「将第一行用作标题」
• 此时已经得到了正确的表结构，还可以顺便做数据清洗（比如修改数据类型、过滤空值）
• 点击「主页」→「关闭并上载至」→选择「仅创建连接」，然后右键这个连接→「加载到」→选择「SQL Server」，填写服务器名、数据库、目标表名，完成导入

最优方案总结 #

• 一次性导入：选方案一，最快最省心
• 重复/自动化导入：选方案二（SSIS），长期来看效率最高
• 代码控/需要嵌入SQL流程：选方案三的动态SQL版本
• 需要数据清洗：选方案四，Power Query的可视化操作更方便

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