在做数据仓库时,最重要的就是ETL的开发,而在ETL开发中的第一步,就是要从原OLTP系统中抽取数据到过渡区中,再对这个过渡区中的数据进行转换,最后把经过处理的干净的数据加载到数据仓库中。
目标数据库是sql server,通过openrowset函数带bulk操作符的用法,导入、导出、更新数据。
带bulk操作符的方式相对于其他方式来说,速度更快。
使用方法:
第1种用法:
openrowset
(
bulk 要加载结果集的文件名,
formatfile = 格式化文件名,
bulk选项
)
第2种用法:
openrowset
(
bulk 文件名,
SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB
)
1、开启即席查询
--修改高级参数
sp_configure 'show advanced options',1
go
--允许即席分布式查询
sp_configure 'Ad Hoc Distributed Queries',1
go
--如果配置的值不在合理范围(在最小值最大值范围内),那么可以强制覆盖
reconfigure with override
go
2、第1种用法,把bcp导出的数据,导入数据库中
具体导入数据时,可以先用bcp把表的格式文件倒出来,然后在把数据导进去,这样按照格式自动转化。
select *
from
openrowset(bulk 'c:\calendar.txt', --要读取的文件路径和名称
formatfile='c:\calendar.fmt', --格式化文件的路径和名称
firstrow = 1, --要载入的第一行
--lastrow = 1000, --要载入的最后一行,此值必须大于firstrow
maxerrors = 10, --在加载失败之前加载操作中最大的错误数
errorfile ='c:\calendar_error.txt', --存放错误的文件
rows_per_batch = 100 --每个批处理导入的行数
) as calendar
3、第2种用法
select 1,
BulkColumn --这个是从数据源获取的,要插入的列,默认的列名
from openrowset(
BULK 'c:\helloworld.class',--指定文件路劲,这里是本地路径
SINGLE_BLOB --指定二进制数据是SINGLE_BLOB
--指定文本是SINGLE_CLOB、SINGLE_NCLOB
)X --必须要别名
select 1,
binColumn --引用后面定义的列别名
from openrowset(
BULK 'c:\Katongxiar01.jpg', --指定文件路劲,这里是本地路径
SINGLE_BLOB --指定二进制数据是SINGLE_BLOB
--指定文本是SINGLE_CLOB、SINGLE_NCLOB
)X(binColumn) --这里还定义了列的别名
把bmp文件直接更新到字段pic中,直接从txt文件查询数据作为1列显示:
update t
set pic = (
select BulkColumn
from openrowset(bulk 'c:\dwhj.bmp',
SINGLE_BLOB) AS X --一定不要忘了定义别名
select 1,
binColumn --引用后面定义的列别名
from openrowset(
BULK 'c:\calendar.txt', --指定文件路劲,这里是本地路径
SINGLE_CLOB --指定二进制数据是SINGLE_BLOB
--指定文本是SINGLE_CLOB、SINGLE_NCLOB
)X(binColumn) --这里还定义了列的别名
联系客服