SQL Server 与 Access、Excel 数据转换方法

SQL Server 与 Access 的数据转换

第1步：通过企业管理器（DTS）图形化导入

对于不熟悉代码的用户，可以通过 SQL Server 自带的 Data Transformation Services (DTS) 向导完成从 Access (.mdb) 到 SQL Server 的数据迁移：

1. 启动向导：在 SQL Server Enterprise Manager（企业管理器）中，依次选择 Tools → Data Transformation Services → Import Data。
2. 选择数据源：在 Choose a Data Source 对话框中选择 Microsoft Access as the Source，键入或浏览选择你的 .mdb 文件路径。
3. 设定目标服务器：在 Choose a Destination 对话框中选择 Microsoft OLE DB Provider for SQL Server，输入服务器地址并选择相应的验证方式。
4. 执行复制：在 Specify Table Copy 界面点击 Copy tables，并在 Select Source Tables 中勾选需要导入的表，最后点击完成即可。

第2步：利用 T-SQL 语句进行数据交互

在 Transact-SQL 中，主要使用 OpenDataSource 和 OPENROWSET 函数连接远程 Access 数据源。以下以 Jet.OLEDB.4.0 为提供程序，假设本地 Access 文件位于 C:\DB.mdb，用户名为 Admin 且无密码：

1. 查询 Access 数据至 SQL Server

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

2. 将 Access 表导入 SQL Server 新建表

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

3. 将 SQL Server 数据插入 Access 表

将指定列从 SQL Server 表复制到 Access 表的现有表中：

INSERT INTO OpenDataSource(
    'Microsoft.Jet.OLEDB.4.0',
    'Data Source="c:\\DB.mdb";User ID=Admin;Password='
)...Table_Name (Column1, Column2)
SELECT Column1, Column2 FROM sql_table_name

或者使用 OPENROWSET 完整表结构复制（例如从 SQL Server 库中的 Test 表复制到 Access 的 Test 表）：

INSERT INTO OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:\\db.mdb';'admin';'', Test)
SELECT id, name FROM Test

SQL Server 与 Excel 的数据转换

第3步：SQL Server 查询 Excel 数据

可以使用 OpenDataSource 配合 Excel OLE DB 提供程序直接查询 Excel 文件内容。注意 Excel 中的工作表名需加方括号和美元符号（如 [Sheet1$]）。

SELECT * 
FROM OpenDataSource(
    'Microsoft.Jet.OLEDB.4.0',
    'Data Source="c:\\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'
)...[Sheet1$]

第4步：将 Excel 数据导入 SQL Server

使用 SELECT ... INTO 语法可以将 Excel 中的数据批量倒入 SQL Server 形成新表：

SELECT * into newtable 
FROM OpenDataSource(
    'Microsoft.Jet.OLEDB.4.0',
    'Data Source="c:\\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'
)...[Sheet1$]

第5步：将 SQL Server 数据导出为 Excel 文件

利用系统存储过程 master..xp_cmdshell 结合命令行工具 bcp，可以将查询结果直接导出为 .xls 格式：

EXEC master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'

参数说明：S 为 SQL 服务器名，U 为用户名（通常为 sa），P 为密码。也可通过 VB6 等语言调用 ADO 执行此命令来触发导出。

第6步：向 Excel 插入/更新数据

如果需要对 Excel 表格写入数据，可使用 INSERT INTO 语句：

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)

另一种写法是直接使用 OPENDATASOURCE 指定 Excel 8.0 属性：

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')

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