【数据快速导入】:Access/Excel数据快速导入SQL Server数据库

yumo6662天前技术文章4

使用 OPENROWSET 和 OPENDATASOURCE 将 Access 数据导入 SQL Server

当我们需要将数据从 Microsoft Access 数据库迁移到 SQL Server。SQL Server 提供了两种强大的方法来实现这一目标：OPENROWSET 和 OPENDATASOURCE。本文将详细介绍如何使用这两种方法将 Access 数据库中的 ML_TF表数据导入到 SQL Server的ML_TF表中。

准备工作

在开始之前，请确保满足以下前提条件：

  1. 已安装 Microsoft Access Database Engine（ACE OLEDB 驱动程序）
  2. SQL Server 服务账户对 Access 数据库文件有读取权限
  3. SQL Server 服务器已启用 Ad Hoc Distributed Queries

启用 Ad Hoc Distributed Queries

在使用 OPENROWSET 或 OPENDATASOURCE 之前，需要先启用 Ad Hoc Distributed Queries：


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

方法一：使用 OPENROWSET

OPENROWSET 函数提供了一种在 SQL Server 中访问远程数据的方法，无需预先设置链接服务器。

基本语法


INSERT INTO target_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path_to_access_db;',
    'SELECT * FROM source_table'
);

实际示例


-- 导入所有数据
INSERT INTO ml_tf (id, name, value, created_date)
SELECT id, name, value, created_date
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\data\your_database.accdb;Persist Security Info=False;',
    'SELECT * FROM ml_tf'
);

-- 导入特定条件的数据
INSERT INTO ml_tf (id, name, value, created_date)
SELECT id, name, value, created_date
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\data\your_database.accdb;',
    'SELECT * FROM ml_tf WHERE value > 100 AND created_date >= #2023-01-01#'
);

方法二：使用 OPENDATASOURCE

OPENDATASOURCE 函数提供了另一种访问远程数据的方法，它不使用链接服务器名称，而是提供特殊的连接信息。

基本语法


INSERT INTO target_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM OPENDATASOURCE(
    'Microsoft.ACE.OLEDB.12.0',
    'Data Source=path_to_access_db;'
)...source_table;

实际示例


-- 导入所有数据
INSERT INTO ml_tf (id, name, value, created_date)
SELECT id, name, value, created_date
FROM OPENDATASOURCE(
    'Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\data\your_database.accdb;'
)...ml_tf;

-- 导入特定列的数据
INSERT INTO ml_tf (id, name, value)
SELECT id, name, value
FROM OPENDATASOURCE(
    'Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\data\your_database.accdb;'
)...ml_tf;

处理不同版本的 Access 数据库

对于 .accdb 格式（Access 2007及以上版本）


-- 使用 ACE OLEDB 12.0 提供程序
INSERT INTO ml_tf (id, name, value, created_date)
SELECT id, name, value, created_date
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\data\your_database.accdb;',
    'SELECT * FROM ml_tf'
);

对于 .mdb 格式（Access 2003及以下版本）


-- 使用 Jet OLEDB 4.0 提供程序
INSERT INTO ml_tf (id, name, value, created_date)
SELECT id, name, value, created_date
FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\your_database.mdb;',
    'SELECT * FROM ml_tf'
);

创建链接服务器的替代方法

如果经常需要访问 Access 数据库，可以考虑创建链接服务器：

-- 创建链接服务器
EXEC sp_addlinkedserver 
    @server = 'AccessLink',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @srvproduct = 'OLE DB Provider for ACE',
    @datasrc = 'C:\data\your_database.accdb';

-- 使用四部分名称查询
INSERT INTO ml_tf (id, name, value, created_date)
SELECT id, name, value, created_date
FROM AccessLink...ml_tf;

常见问题及解决方案



1. 权限问题

确保 SQL Server 服务账户对 Access 数据库文件有读取权限。如果遇到权限问题，可以尝试：

  • 将 Access 数据库文件移动到 SQL Server 服务账户有访问权限的目录
  • 修改 SQL Server 服务账户的权限设置

2. 驱动程序问题

如果遇到 "未注册的提供程序" 错误，请确保已安装正确版本的 Microsoft Access Database Engine：

  • 对于 32 位 SQL Server，安装 32 位版本的 ACE 驱动程序
  • 对于 64 位 SQL Server，安装 64 位版本的 ACE 驱动程序

3. 路径问题

使用完整路径而不是相对路径，并确保路径中使用正确的斜杠：

-- 正确示例
'Data Source=C:\data\your_database.accdb;'

-- 错误示例
'Data Source=.\data\your_database.accdb;'

验证导入结果

导入完成后，建议验证数据是否正确导入：

-- 检查导入的行数
SELECT COUNT(*) AS ImportedRows FROM ml_tf;

-- 查看前10行数据
SELECT TOP 10 * FROM ml_tf;

-- 比较源表和目标表的数据
SELECT 
    (SELECT COUNT(*) FROM OPENROWSET(...)...ml_tf) AS SourceCount,
    (SELECT COUNT(*) FROM ml_tf) AS TargetCount;

性能优化建议

  1. 批量插入：对于大量数据，考虑使用批量插入操作
  2. 索引管理：在导入前删除目标表上的索引，导入完成后重新创建
  3. 事务管理：对于大型数据集，合理使用事务以避免日志文件过大
  4. 分批处理：如果数据量非常大，考虑分批导入数据

总结

使用 OPENROWSET 和 OPENDATASOURCE 是将 Access 数据导入 SQL Server 的有效方法。这两种方法都提供了灵活的数据访问方式，无需预先设置链接服务器。选择哪种方法取决于具体需求和个人偏好。

  • OPENROWSET 更适合单次或偶尔的数据导入操作
  • OPENDATASOURCE 语法更简洁，但功能稍有限制
  • 对于频繁的数据访问，考虑创建链接服务器

无论选择哪种方法，都要确保满足前提条件，特别是正确安装 OLEDB 驱动程序并设置适当的权限。通过遵循本文中的指南和最佳实践，您可以成功地将 Access 数据库中的 ml_tf 表数据导入到 SQL Server 表中。



Excel数据快速导入SQL Server数据库表

--OPENDATASOURCE
INSERT INTO dept (dep_no, dep_name, top_dep ,sys_man, sys_dd, bstop, rem)
SELECT dep_no, dep_name, top_dep ,sys_man, sys_dd, bstop, rem
FROM OPENDATASOURCE(
'Microsoft.ACE.OLEDB.12.0',
'Data Source=D:Data\部门表20250901_1719.xlsx;Extended Properties="Excel 12.0;HDR=YES;IMEX=1"'
)...[Sheet1$];

--OPENROWSET

INSERT INTO dept (dep_no, dep_name, top_dep, sys_man, sys_dd, bstop, rem)
SELECT dep_no, dep_name, top_dep, sys_man, sys_dd, bstop, rem
FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=D:\Data\部门表20250901_1719.xlsx; HDR=YES; IMEX=1',
'SELECT dep_no, dep_name, top_dep, sys_man, sys_dd, bstop, rem FROM [Sheet1$]'
);
返回列表

上一篇：EXCEL小白的第一座金矿:SUM函数全解，从入门到精通

下一篇：筛选条件下提取数据如你没有新函数就仔细看看这篇吧_1954

相关文章

code函数是一个超级转换器 根据ABCD等级的变化，判断等级升降情况

问题求助SOS：如何根据ABCD等级的变化，判断升降情况？这个问题的提干很简单，非常容易描述。如下图所示：A列是8月份的各等级信息，B列是8月过渡到9月时，等级的变化情况，我们想要判断一下，9月相对于...

EXCEL小白的第一座金矿:SUM函数全解，从入门到精通

一、SUM 函数基础 —— 初出茅庐学求和SUM 函数，简单来说，就是把你指定的一堆数字加起来。它的语法格式就像这样：=SUM (数值 1,[数值 2,...]) 。这里的数值 1 是必须要有的，数值...

筛选条件下提取数据如你没有新函数就仔细看看这篇吧_1954

筛选条件下提取数据，如你没有新函数，就仔细看看这篇吧！筛选一开，VLOOKUP直接抓瞎，谁懂？2024-06-11，Excel圈炸锅：大神甩出一条逆天数组公式，专治“筛选后数据失踪”的老毛病。一句话：...

Excel日期天数秒提取!DAY函数简单高效

还在手动记录日期中的天数？DAY函数是您的“日期挖掘器”，一键从日期中精准提取天数，让日程安排、到期计算、周期统计变得无比轻松！一、一句话理解DAY是做什么的DAY函数只做一件事：从日期中提取天数，返...

Python 中 必须掌握的 20 个核心函数——values()函数

values()是Python字典对象的方法，用于返回字典中所有值的视图对象。它提供了对字典值的高效访问和操作。一、values()的基本用法1.1 方法签名dict.values()返回：字典值的视...

滇ICP备2024046894号-16