使用OPENROWSET(BULK...)从平面文件加载数据
要使用OPENROWSET首先要开启一个configure选项
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1; --Switch on specific functionality
GO
RECONFIGURE;
GO
OPENROWSET可以把整个flatfile读取成二进制数据流,这个时候你需要自己再用cast+substring的方法去截取数据成为栏位
SELECT *, CAST(BulkColumn as varchar(max)) FROM OPENROWSET(BULK N'D:\Test.txt', SINGLE_BLOB) AS Document;
另一种做法是用formatfile,这样select出来的数据栏位已经按formatfile定义好的格式显示出来
SELECT a.* FROM OPENROWSET( BULK 'D:\Test.txt', FORMATFILE = 'D:\test.fmt') AS a;
那formatfile怎么生成的呢?
bcp JerryDB.dbo.ssis_merge_test format nul -c -f D:\test.fmt -S JERRYCHENLAP\MSSQLSERVER2014 -T
上面的命令行生成下面的数据到D:\test.fmt这个文件
10.0
3
1 SQLCHAR 0 24 "\t" 1 col1 ""
2 SQLCHAR 0 100 "\t" 2 col2 Chinese_PRC_CI_AS
3 SQLCHAR 0 12 "\r\n" 3 col3 ""
加个-x可以变成xml格式的
bcp JerryDB.dbo.ssis_merge_test format nul -c -x -f D:\test.fmt -S JERRYCHENLAP\MSSQLSERVER2014 -T
上面的命令行生成下面的数据到D:\test.fmt这个文件
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="24"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="12"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="col1" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="2" NAME="col2" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="col3" xsi:type="SQLINT"/>
</ROW>
</BCPFORMAT>
参考:
创建格式化文件
https://technet.microsoft.com/zh-cn/library/ms191516(v=sql.90).aspx
OPENROWSET (Transact-SQL)
https://msdn.microsoft.com/zh-cn/library/ms190312.aspx
使用格式化文件大容量导入数据
https://technet.microsoft.com/zh-cn/library/ms178129(SQL.90).aspx
最新文章
- IdentityHashMap类:增加相同Key(键)内容的结果
- 函数指针_typedef
- 最好用的placeholder插件,jQuery插件EnPlaceholder
- 【翻译二十二】java-并发之集合与原子变量
- Codeforces Round #199 (Div. 2)
- win7启动文件修复
- Android 横屏时禁止输入法全屏
- [HDOJ1811]Rank of Tetris(并查集、拓扑排序)
- C#列表顺序替换思想
- python文件处理及装饰器
- OpenCV学习 7:图像形态学:腐蚀、膨胀
- 转载:温故而知新 - AngularJS 1.x
- 【Vue 入门】使用 Vue2 开发一个展示项目列表的应用
- Shell脚本了解
- cas单点登录防止登出退出后刷新后退ticket失效报500错
- 批量修改所有服务器的dbmail配置
- 获取日期Date
- Spring Boot 静态资源访问原理解析
- 转 Configuring Relationships with the Fluent API
- LOJ #2802. 「CCC 2018」平衡树(整除分块 + dp)