问题
设计
创建一个BCP格式的文件用于把想导入的文本文件信息映射到目标SQL表,然后把上述格式的文件作为参数传给命令行工具bcp.exe。
方案
假设SQL表定义如下:
create table tblTestCases ( caseID char(4) primary key, input char(3) not null, expected int not null ) |
还有一个包含测试用例数据的文本文件叫作newData.dat
0020,e13,66
0021,e14,77
0022,e15,88
0023,e16,99
0024,e17,66
创建一个叫newData.fmt的BCP格式的文件,这个文件采用制表符(tab)作为分隔符。
8.0
3
1 SQLCHAR 0 4 "," 1 caseID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "," 2 input SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 2 "\r\n" 3 excepted SQL_Latin1_General_CP1_CI_AS
用于导入测试用例数据的命令如下:
C:\>bcp.exe dbTestCasesAndResults..tblTestCases in newData.dat -fnewData.fmt -S. -UtestLogin -Psecret
此命令的意思是针对数据库dbTestCasesAndResults里的表tblTestCases运行BCP程序,把newData.dat里的数据按照newData.fmt所定义的格式映射导入到上述表中。这些命令是针对本地的SQLServer所执行的,连连数据库的时候使用叫作 testLogin的SQL登录账号,SQL密码是secret。
使用这种技术,关键是要理解bcp.exe程序所使用的格式文件的结构。这个文件的第1行只有一个单独的值用来表示sql server的版本号。SQL Server 7.0的版本号是7.0,SQL Server 2000的版本号是8.0,SQL Server2005的版本号是9.0。格式文件的第二行是一个整数值,它表示格式文件中映射实际开始的行号。第3行以后的的内容都是映射信息。每个映射行(mapping line)有8个列。前5个列代表与输入数据(本例中指文件文件)有关信息。后3个列代表要导入的目标信息(本例中指SQL表)。第一列其实就是从1开始的系列数字。这些值总是1、2、3等,依此类推。(这些数字以及其他一些BCP格式文件里的信息看上去是没有必要的,但是在其他一些情况下确实会用到它们。)映射行里的第二列是要导入的数据类型。当从文本文件导入数据的时候,不管这个值代表的是什么,它的类型总是SQLCHAR。第三例是前缀长度(prefix length)。这是当进行从SQL到SQL数据拷贝时,BCP用于优化的一个相当复杂的参数。幸运的是,当把文本数据导入SQL的时候,前缀总长度值总是0.第四列表示输入字段字符的最大长度。第五列表示字段分隔会,在此用逗号来分割所有的字段。比如说,如果输入数据文件的字段是用制表符来分割,应该在映射文件中指定为\t。第六列到第八列指代的是目标SQL表,而不是输入文件。第六列和第七列分别指SQL表里相应列的顺序和名称。映射行第八列用来指定要使用的SQL排序规则。