SQL Server 与 Excel Access 数据表的导入导出(注：参照博园.NET大观)

我们知道在Sql Server 中 集成了数据的导入导出这么一个工具，那用Sql 脚本怎么轻松操作 Access 和 Excel 中的数据呢，接下来我们看一看：

一、 SQL SERVER 和EXCEL的数据导入导出，

A 查询Excel里的数据

SELECT *

FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="f:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

简单解析：

OpenDataSource，一个系统里的函数，如果不是很清楚可以点这个，/zh-cn/library/ms179856.aspx，

简单的理解就是提供一个特殊的连接，在这里我们可以理解成打开Access,和Excel的连接。

上面的脚本也可以写成下面这样，

SELECT *

FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="f:\test.xls";Extended properties=Excel 5.0')...Sheet1$

执行结果如：

B 将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$]

C 将SQL SERVER中查询到的数据导成一个Excel文件

T-SQL代码：

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

参数：S 是SQL服务器名；U是用户；P是密码

说明：还可以导出文本文件等多种格式

实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'

二、SQL SERVER 和Accss的数据导入导出

A 在SQL SERVER里查询access数据:

SELECT *

FROM OpenDataSource（ 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\DB.mdb";User ID=Admin;Password='）...表名

B. 将access导入SQL server

在SQL SERVER 里运行:

SELECT *

INTO newtable

FROM OPENDATASOURCE （'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\DB.mdb";User ID=Admin;Password=' ）...表名

C. 将SQL SERVER表里的数据插入到Access表中

在SQL SERVER 里运行：

insert into OpenDataSource（ 'Microsoft.Jet.OLEDB.4.0',

'Data Source=" c:\DB.mdb";User ID=Admin;Password='）...表名

（列名1,列名2）

select 列名1,列名2 from sql表

实例：

insert into OPENROWSET（'Microsoft.Jet.OLEDB.4.0',

'C:\db.mdb';'admin';'', Test）

select id,name from Test

INSERT INTO OPENROWSET（'Microsoft.Jet.OLEDB.4.0', 'c:\trade.mdb'; 'admin'; '', 表名）

SELECT *

FROM sqltablename

如果运行时，出现如下错误如：

消息 15281，级别 16，状态 1，第 1 行

SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。

有两种解决办法（我现在知道的）

1可以采用以下通过启用Ad Hoc Distributed Queries解决：

exec sp_configure 'show advanced options',1

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1

reconfigure

使用完成后，关闭Ad Hoc Distributed Queries：

exec sp_configure 'Ad Hoc Distributed Queries',0

reconfigure

exec sp_configure 'show advanced options',0

reconfigure

2 种是 在“开始”——》“所有程序”——》”Microsoft SQL Server “ 的 ”配置工具“ ——》”SQL Server 外围应用配置器“，打开以后如下图：

然后点击，如：

然后在”启用 OPENROWSET 和 OPENDATASOURCE 支持“打上钩“，确定即可，如：