SQL存储过程测试——使用BCP工具导入测试用例数据

问题

 

      如何使用BCP从一个文本文件把测试用例数据导入到SQL表

 

设计

 

      创建一个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排序规则。

 

注解

 

      使用BCP工具可以高效地以自动化的方式把测试用例数据从文本文件引入到SQL测试用例表中。特别注意，测试用例的数据文件在数据块的最后一行之后一定不能再有换行符。如果有换行符，就会被BCP解释成一个新的空行。而用于映射的格式文件在最后一行之后一定要有一个换行符。如果没有这个换行符，BCP就不会读入映射文件的最后一行。

 

      即使文本文件的数据格式与SQL表的结果不完全吻合，也可以通过BCP工具从文本文件导入数据。换句话说，即使文本文件的数据与相应的SQL列顺序不一致或者文本文件的数据有多作的字段，仍然可以使用BCP工具来导入数据，例如：

 

0020,66,useless,e13

0021,77,no-need,e14

0022,88,go-away,e15

0023,99,drop-it,e16

 

      这个文件有些额外的信息并不想将其导入，而且各个字段（caseID,expected value,unneeded data,input value）的顺序也和SQL列（caseID,input,expected）的顺序不一致。针对这个文本文件的BCP文件的格式如下：

 

8.0

4

1      SQLCHAR       0      4      ","      1      caseID      SQL_Latin1_General_CP1_CI_AS

2      SQLCHAR       0      2      ","      3      excepted      SQL_Latin1_General_CP1_CI_AS

3      SQLCHAR       0      7      ","      0      junk      SQL_Latin1_General_CP1_CI_AS

4      SQLCHAR       0      3      "/r/n"      2      input      SQL_Latin1_General_CP1_CI_AS

 

      映射文件的第6列通过设置该列的值来指定文本文件中相应数据的插入顺序，如果该列的值设为0，则在插入数据的时候忽略这个列。

 

      因为bcp.exe是一个命令行程序，所以可以手动运行它也可以把想要执行的命令放到一个简单的BAT文件，然后在自己的程序里调用这个BAT文件。如果想在SQL环境下使用BCP，可以通过BULK INSERT 命令来完成。

 

bulk insert dbTestCasesAndResults..tblTestCases

from 'C:/somewhere/newData.dat'

with (formatfile = 'C:/somewhere/newData.fmt')