用OPENROWSET读取文件数据

最新推荐文章于 2025-05-01 18:29:01 发布

s115859272

最新推荐文章于 2025-05-01 18:29:01 发布

阅读量461

点赞数

分类专栏：数据库操作文章标签： sqlserver microsoft 服务器 collation conflict sql server

数据库操作专栏收录该内容

8 篇文章

订阅专栏

Joseph 今天碰到用 Sqlserver 直接从外部读取文件数据的问题，这个问题我以前也碰到过，找了半天只能找到将数据读入临时表再处理的办法，最后不了了之。其实这个功能在 Oracle 中可以用外部表很简单的解决。今天又 google 了一下（有事没事都找 google），找到了 OPENROWSET 这个好东西，看来 Sqlserver 还是有很多功能都没有用过，等待我们慢慢挖掘。

基本语法：

OPENROWSET ( ' provider_name '
    , { 'datasource' ; 'user_id' ; 'password'
        | ' provider_string ' }
     , { [ catalog.] [ schema .] object
        | ' query ' }
     )

详细介绍可以看 SQLSERVER 联机帮助。

具体操作：
1.创建一个 txt 文本，里面输入一些数据。第一行必须是列名，列之间必须用" , " 分隔（我试了下空格和制表符都不行，不知道这个间隔符可不可以该的），另外默认的数据类型是这样的：如果是纯数字会转换为相应的数字类型，还有字符的话会转换为字符类型，如果中间有 $ 符会转换为 money 类型（其他货币符号我没有尝试），如果在数据两边加上双引号(")，会强制转为字符类型。

a.txt：
DMDUNIT,DMDGROUP,LOC
"1001","14","1401"

2.写查询语句：

select * from
OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:/;','select * from a.txt')

OK，搞定，联机帮助上说：“依据 OLE DB 提供程序的能力，还可以将 OPENROWSET 函数引用为 INSERT、UPDATE 或 DELETE 语句的目标表。”不过，'MSDASQL' 似乎没有提供这种能力，我尝试了 INSERT、UPDATE、DELETE 操作，返回错误：

服务器: 消息 7390，级别 16，状态 1，行 1
The requested operation could not be performed because the OLE DB provider 'MSDASQL' does not support the required transaction interface.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface returned 0x80004002].

如果你还不满足，想做些表连接什么的，没问题：

select A.* from DMDUNIT A
RIGHT OUTER JOIN
OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:/;','select * from a.txt') B
ON A.DMDUNIT=B.DMDUNIT

PS：其实用 OpenRowset 也可以解决不同服务器上数据库的表连接问题：

SELECT * FROM 表a a
JOIN OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=服务器2;UID=用户名;PWD=密码', 库B.所有者.表b) b on a.field=b.field

排序规则的问题：
当用 OpenRowset 进行表连接的时候，一般都会碰到排序规则冲突的问题，不知道用 OpenRowset 查询时默认的排序规则是什么，不同排序规则的列做连接时会产生如下错误：

服务器: 消息 446，级别 16，状态 9，行 1
Cannot resolve collation conflict for equal to operation.

解决方法：
在连接的列上制定排序规则：

select A.* from DMDUNIT A
WHERE NOT EXISTS
(SELECT 1 FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:/;','select * from a.txt') B
WHERE A.DMDUNIT=B.a COLLATE Chinese_PRC_BIN)

参考资料：
OPENROWSET：
http://msdn2.microsoft.com/zh-cn/library/ms190312(SQL.90).aspx

使用排序规则：
http://msdn2.microsoft.com/zh-cn/library/ms187582(SQL.90).aspx

排序规则简介：
http://www.100pig.com/blog/article.asp?id=28

不同服务器数据库表连接：
http://u3.club.163.com/viewElite.m?catalogId=7023&eliteId=7023_1056e1a823c0f5a