SQL 远程访问和文件访问示例

最新推荐文章于 2024-11-28 17:00:00 发布

原创最新推荐文章于 2024-11-28 17:00:00 发布 · 186 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL #SQL Server #Microsoft #Security #Access

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

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]