SQL 远程访问和文件访问示例-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_19838/article/details/82045575

本文介绍了如何使用SQL查询远程数据库，并结合文件读取技术进行数据整合和分析，涵盖了从连接数据库到数据筛选、聚合等核心操作，以及如何通过文件读取实现外部数据的集成。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Remote Access

select * from opendatasource('sqloledb','server=192.168.1.1;uid=sa;pwd=123456;database=TT').TT.dbo.test1

select top 10 * from opendatasource('sqloledb','Data Source=MSOutputDB3;Initial Catalog=TimeSeries;;Integrated Security=SSPI;').TimeSeries.dbo.NetAssets
-----------------------------------------------------------------------------------

File access sample

schema.ini Sample
[US_Other_Funds_20090416.txt]
ColNameHeader = True
Format=TabDelimited
MaxScanRows=0

select SecId+',' from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=D:\Data;Format=TabDelimited;',
'select * from [US_Other_Funds_20090416.txt]')

select SecId,Year
from opendatasource('sqloledb','Data Source=MSOutputDB3;Initial Catalog=TimeSeries;;Integrated Security=SSPI;').TimeSeries.dbo.NetAssets
where SecId in
(select SecId from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\Data;Format=TabDelimited;',
'select * from [US_Other_Funds_20090416.txt]'))

----------------------------------------------------------------------------------
Another sample

select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Text;HDR=Yes;DATABASE=D:\Data\FullIdList'
,'select * from [b.txt]')

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\TEXT]