SQL SERVER 与ACCESS、EXCEL的数据转换

演示示例
--====== access 2003 
--1.在SQL SERVER里查询access数据:
SELECT * 
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="c:\DB.mdb";User ID=Admin;Password=')...表名
--2.从access中导入数据到SQL server,sql server源表不存在时.
SELECT * INTO newtable
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="c:\DB.mdb";User ID=Admin;Password=')...表名
--3.将SQL SERVER表里的数据导入到Access表中
INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=" c:\DB.mdb";User ID=Admin;Password=')...表名(列名1,列名2)
SELECT 列名1,列名2 FROM SQL表

-- OPENROWSET 方式
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\access.mdb'; 'admin'; '', 表名)
SELECT * FROM sqltablename

--===SQL SERVER 和EXCEL的数据导入导出,注意:EXCEL文件必须存在于所运行的SQL SERVER 服务器中.(注意这种现象:本地EXCEL文件,异地SQL 服务器)是不行的.
--1、在SQL SERVER里查询Excel数据:
SELECT * 
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\book1.xls',sheet1$)

--2、将Excel的数据导入SQL server :--导入数据并生成 新表
SELECT * INTO 表
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
--导入数据到已存在的表. 
INSERT INTO 表 
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\test.xls',[sheet1$])

--插入数据到Excel
--如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:
INSERT INTO OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',[sheet1$])
SELECT * FROM tabelName

--使用子查询插入数据.
INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="c:\book.xls"; User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] (a,a1,a2) 
SELECT 'a1','a2','a3' FROM tabelName

--插入一行数据到Excel表
INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="c:\Temp.xls";
User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$](A1,A2,A3) values (1,2,3)

 

 

使用这两个函数是OPENDATASOURCE和OPENROWSET。它们的功能基本上相同,不同之处主要有两点.
(1) 调用方式不同。
OPENDATASOURCE的参数有两个,分别是OLE DB Provider和连接字符串。
******使用OPENDATASOURCE只相当于引用数据库或者是服务（对于SQL Server、Oracle等数据库来说）。********
要想引用其中的数据表或视图,必须在OPENDATASOURCE(...)后进行引用。

在SQL Server中通过OPENDATASOURCE查询Access数据库abc.mdb中的table1表
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Provider=Microsoft.Jet.OLEDB.4.0;Data Source=abc.mdb;Persist Security Info=False')...table1
-- 访问 ACCESS 2007
SELECT * FROM
OPENDATASOURCE('Microsoft.Ace.OLEDB.12.0','Provider=Microsoft.Ace.OLEDB.12.0;
Data Source=C:\access2007.accdb;Persist Security Info=False')...table

***** OPENROWSET相当于一个记录集,可以将直接当成一个表或视图使用。 ********
在SQL Server中通过OPENROWSETE查询Access数据库abc.mdb中的table1表
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','abc.mdb';'admin';'','SELECT * FROM table1')
--访问 ACCESS 2007
SELECT * FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0','c:\access2007.accdb';'admin';'',table)

(2) 灵活度不同。
OPENDATASOURCE只能打开相应数据库中的表或视图,如果需要过滤的话,只能在SQL Server中进行处理。
而OPENROWSET可以在打开数据库的同时对其进行过滤,如上面的例子,
在OPENROWSET中可以使用SELECT * FROM table1对abc.mdb中的数据表进行查询,
而OPENDATASOURCE只能引用table1,而无法查询table1。

因此，OPENROWSET比较OPENDATASOURCE更加灵活。