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