SQL Server与Access、Excel数据互转：T-SQL实战指南

准备工作：启用高级选项

在使用部分高级功能（如通过OS命令行导出）之前，需确保服务器已开启相应配置。虽然基本的链接服务器查询通常无需额外设置，但为了覆盖所有场景，建议确认以下选项处于开启状态（以sysadmin角色执行）：

1. 启用Ad Hoc Distributed Queries：这是使用 OPENROWSET 或 OPENDATASOURCE 访问OLE DB数据源的前提。
2. 启用xp_cmdshell：若计划使用 bcp 命令行工具进行批量导出，必须开启此扩展存储过程。

可使用如下SQL检查并开启：

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
-- 仅当需要使用 bcp 导出时才开启下面这项
-- EXEC sp_configure 'xp_cmdshell', 1;
-- RECONFIGURE;

一、SQL Server 与 Access (.mdb) 数据转换

Access文件本质上是Jet引擎管理的数据库。我们可以通过OLE DB Provider将其视为外部表进行读写操作。核心驱动字符串通常为 Microsoft.Jet.OLEDB.4.0。

第1步：从 Access 读取数据到 SQL Server

目的：查询或获取Access文件中的数据，并在SQL Server中作为结果集或临时表使用。

方法A：直接查询（使用 OpenDataSource）

SELECT * FROM OpenDataSource (
    'Microsoft.Jet.OLEDB.4.0', 
    'Data Source="C:\\DB.mdb";User ID=Admin;Password='
)...表名

方法B：复制到新表（使用 INTO）

将Access中的数据直接导入SQL Server创建新表：

SELECT * INTO newtable 
FROM OPENDATASOURCE (
    'Microsoft.Jet.OLEDB.4.0', 
    'Data Source="C:\\DB.mdb";User ID=Admin;Password='
)...表名

第2步：将 SQL Server 数据写入 Access

目的：将SQL Server中的现有表数据追加或合并到Access表中。

注意：Access表结构需预先存在。此操作相当于SQL中的INSERT。

INSERT INTO OpenDataSource ( 
    'Microsoft.Jet.OLEDB.4.0', 
    'Data Source="C:\\DB.mdb";User ID=Admin;Password=' 
)...目标表名 (列名1,列名2) 
SELECT 列名1, 列名2 
FROM sql_server_table

实例演示（使用 OPENROWSET）：

-- 将ID和NAME从SQL的Test表插入到Access的Test表
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\db.mdb';'admin';'', Test)
SELECT id, name FROM Test

第3步：全表数据迁移

若要将SQL Server某表的全部内容推送到Access指定表中：

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\trade.mdb'; 'admin'; '', '表名')
SELECT * FROM sqltablename

二、SQL Server 与 Excel (.xls/.xlsx) 数据转换

Excel在早期版本中被当作数据库对待，其工作表名称后需加美元符号 $ 才能作为表名引用。连接属性需注意区分Excel 97-2003 (Excel 5.0) 和更高版本的格式支持（但在Jet引擎下通常统一使用 Extended Properties=Excel 8.0 或 5.0 即可覆盖大部分.xls文件）。

第1步：从 Excel 读取数据

入口与动作：在SQL Server Management Studio中执行SELECT语句。

-- 假设Excel路径为 C:\book1.xls，查询 Sheet1 的所有数据
SELECT * FROM OpenDataSource (
    'Microsoft.Jet.OLEDB.4.0', 
    'Data Source="C:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'
)...[Sheet1$]

如果第一行包含列名，SQL Server会自动将第一行识别为字段名。如需显式指定范围或避免首行被误认为列头，可进一步配置连接字符串或使用命名区域，但常规 [SheetName$] 是最简用法。

第2步：将 Excel 导入 SQL Server

操作顺序：直接将查询到的Excel结果集存入SQL Server新表。

SELECT * INTO new_excel_table 
FROM OpenDataSource (
    'Microsoft.Jet.OLEDB.4.0', 
    'Data Source="C:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'
)...xactions

第3步：将 SQL Server 数据导出为 Excel

风险提示：此方法依赖服务器端安装Excel相关组件或驱动程序，且对目录权限有要求。

工具选择：使用 xp_cmdshell 调用系统 bcp 实用程序。

参数说明：

• S: SQL Server服务器名称
• U: 用户名
• P: 密码
• c: 字符类型（无引号），适用于大多数情况
• q: 使用双引号标识符

导出整个表：

EXEC master..xp_cmdshell '
   bcp 库名.dbo.表名 out C:\Temp.xls -c -q -S"servername" -U"sa" -P""'

导出查询结果：

EXEC master..xp_cmdshell '
   bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\authors.xls -c -Sservername -Usa -Ppassword'

VB6 应用示例：如果在应用程序层需要触发导出，可通过ADO执行存储过程或直接调用cmdshell：

' VB6 ADO 代码片段
Dim cn As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp \"SELECT col1, col2 FROM 库名.dbo.表名\" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"

第4步：向 Excel 插入数据

注意：向Excel INSERT数据前，Excel文件中必须有对应的Sheet和列结构，否则可能失败。通常建议使用导出逻辑生成文件，再通过Excel宏或脚本填充数据，或者先手动创建一个具有正确表头的空Excel模板作为目标。

T-SQL 插入示例：

-- 插入单行数据
INSERT INTO OpenDataSource(
    'Microsoft.Jet.OLEDB.4.0',
    'Data Source="C:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'
)...table1 (A1,A2,A3) VALUES (1,2,3)

多行/结构化插入示例：

INSERT INTO OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
    'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]
(bestand, produkt)
VALUES (20, 'Test')

常见问题与注意事项

1. 路径权限问题：SQL Server服务通常以 Network Service 或特定账号运行，这些账号可能无法直接访问网络路径或本地文件夹。请确保服务账号对文件所在目录拥有读写权限，尽量使用绝对路径。
2. Excel模式锁定：当SQL Server尝试读取Excel文件时，如果该文件正在被Excel应用程序打开，可能会因独占锁而导致连接失败。请在查询前关闭Excel文件。
3. .xlsx 兼容性问题：上述代码主要基于旧版Jet引擎（.mdb/.xls）。对于新版Office (.xlsx)，建议安装并注册 Microsoft.ACE.OLEDB.12.0 提供者，并将连接字符串替换为 Microsoft.ACE.OLEDB.12.0，同时可能需要调整扩展属性 Excel 12.0 Xml。
4. 安全性警告：开启 xp_cmdshell 会降低数据库服务器的安全性。生产环境中应谨慎评估，使用完毕后及时关闭： EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;。

总结

通过以上T-SQL技巧，开发者和DBA可以在不离开SSMS的情况下，快速实现SQL Server与Access、Excel之间的数据交互。无论是轻量级的报表提取还是大规模的数据清洗准备，这些原生接口都能提供即插即用的便利。

以上就是SQL Server与Access、Excel数据转换的详细内容，更多关于SQL Server数据导入导出的资料请关注本站其它相关文章！