OpenDataSource函数,OPENROWSET函数

--代码如下:

exec sp_configure 'show advanced options',1

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1

reconfigure

--(1)例如现在我想打开D盘中BJData.mdb中的BJAdmin表的信息

select ID,Title,OwnerName from

opendataSource('Microsoft.Jet.OLEDB.4.0','Data Source="D:/BJData.mdb";')...BJAdmin

--(2)我现在要将BJAdmin中BJNewsCategory中的信息插入到A表中

--先查询下Test.Newscategory

select * from  Test.dbo.Newscategory

insert into Newscategory select Title from OpenDataSource

('Microsoft.Jet.OLEDB.4.0','Data Source="D:/BJData.mdb";')...BJNewsCategory

--导入之后再查询

select * from  Test.dbo.Newscategory

--上面的都是在MSSQL中查询Access中的信息,现在将MSSQL中的信息导入到Access中去

--(3)将AdminInfo的信息插入到BJData.mdb中的BJAdmin中去

select * from Test.dbo.AdminInfo

insert into opendatasource('microsoft.Jet.OLEDB.4.0','Data source="D:/BJData.mdb"')...BJAdmin

(Title,RoleID) select Title,1 from AdminInfo

--然后查询BJAdmin表的信息

select ID,Title,OwnerName from

opendataSource('Microsoft.Jet.OLEDB.4.0','Data Source="D:/BJData.mdb";')...BJAdmin

--(4)上面说的是Access与MSSQl之间的操作现在操作下

--Excel与MSSQL之间的操作

--从Excel中读取信息

select * from OpenDataSource

('Microsoft.Jet.OLEDB.4.0','Data Source="D:/work.et";Extended properties=Excel 5.0')...[sheet1$]

insert into opendatasource('SQLOLEDB','Data Source=IP地址;User ID=zhongyi;password=xinrongkeji').sqlzhongyi.dbo.zhongyiAdmin(Title,Password)

select Title,Password from whsxjAdmin

--下面说下OPENROWSET

--(1)直接查询远程服务器上面的信息

select ID,Title from OpenRowset

('SQLOLEDB','IP地址';'zhongyi';'xinrongkeji','select * from sqlzhongyi.dbo.zhongyiAdmin') t

--(2)查询Access里面的信息

select * from OpenRowset

('microsoft.Jet.OLEDB.4.0','d:/BJData.mdb';'Admin';'',BJAdmin) as g