大容量插入格式文件:0行受影响

Question

在Server 2008中使用BULK INSERT命令时，它返回：

(0 row(s) affected)

我使用此命令执行大容量插入：

BULK INSERT Test
    FROM 'C:\DataFiles\Tests.dat'
    WITH (FORMATFILE = 'C:\DataFiles\FormatFiles\TestFormat.Fmt');
GO

Tests.dat包含：

b00d23fe-580e-42dc-abd4-e8a054395126,48dd5dd6e3a144f7a817f234dd51469c,452eb8ce-6ae2-4e7a-a389-1097882c83ab，、160、86400、86400、2004-04-03 23:23:00.000、2012-07-06 13:26:31.633、2012-07-06 13:27:44.650、3、51B7A831-4731-4e2e2e-06636ADC7AD3、0、0、0、字段名1、字段名2、字段名3，字段名4，

格式文件TestFormat.fmt包含：

9.0
39
1       SQLCHAR       0       37      ","      1     Key                                              ""
2       SQLCHAR       0       37      ","      2     TestType                                         ""
3       SQLCHAR       0       37      ","      3     CaseKey                                          ""
4       SQLCHAR       0       30      ","      4     Height                                           ""
5       SQLCHAR       0       30      ","      5     Weight                                           ""
6       SQLCHAR       0       128     ","      6     PacemakerType                                    Latin1_General_CI_AI
7       SQLCHAR       0       0       ","      7     Diary                                            Latin1_General_CI_AI
8       SQLCHAR       0       0       ","      8     Indication                                       Latin1_General_CI_AI
9       SQLCHAR       0       0       ","      9     Medication                                       Latin1_General_CI_AI
10      SQLCHAR       0       37      ","      10    RecorderType                                     ""
11      SQLCHAR       0       100     ","      11    RecorderSerial                                   Latin1_General_CI_AI
12      SQLCHAR       0       0       ","      12    Comments                                         Latin1_General_CI_AI
13      SQLCHAR       0       12      ","      13    Status                                           ""
14      SQLCHAR       0       0       ","      14    AdditionalData                                   Latin1_General_CI_AI
15      SQLCHAR       0       37      ","      15    OrderKey                                         ""
16      SQLCHAR       0       12      ","      16    Duration                                         ""
17      SQLCHAR       0       12      ","      17    Age                                              ""
18      SQLCHAR       0       24      ","      18    RecordingStartDateTime                           ""
19      SQLCHAR       0       128     ","      19    Ward                                             Latin1_General_CI_AI
20      SQLCHAR       0       24      ","      20    CreatedDateTime                                  ""
21      SQLCHAR       0       24      ","      21    UpdatedDateTime                                  ""
22      SQLCHAR       0       21      ","      22    UserGroupBits                                    ""
23      SQLCHAR       0       24      ","      23    LastArchive                                      ""
24      SQLCHAR       0       128     ","      24    PointOfCare                                      Latin1_General_CI_AI
25      SQLCHAR       0       128     ","      25    Bed                                              Latin1_General_CI_AI
26      SQLCHAR       0       37      ","      26    DownloadFacilityKey                              ""
27      SQLCHAR       0       37      ","      27    AnalysisFacilityKey                              ""
28      SQLCHAR       0       12      ","      28    Priority                                         ""
29      SQLCHAR       0       37      ","      29    FacilityKey                                      ""
30      SQLCHAR       0       12      ","      30    PacemakerTypeStandard                            ""
31      SQLCHAR       0       128     ","      31    TestTypeName                                     Latin1_General_CI_AI
32      SQLCHAR       0       128     ","      32    UserDefined1Name                                 Latin1_General_CI_AI
33      SQLCHAR       0       128     ","      33    UserDefined1Value                                Latin1_General_CI_AI
34      SQLCHAR       0       128     ","      34    UserDefined2Name                                 Latin1_General_CI_AI
35      SQLCHAR       0       128     ","      35    UserDefined2Value                                Latin1_General_CI_AI
36      SQLCHAR       0       128     ","      36    UserDefined3Name                                 Latin1_General_CI_AI
37      SQLCHAR       0       128     ","      37    UserDefined3Value                                Latin1_General_CI_AI
38      SQLCHAR       0       128     ","      38    UserDefined4Name                                 Latin1_General_CI_AI
39      SQLCHAR       0       128     "\r\n"   39    UserDefined4Value                                Latin1_General_CI_AI

我搞不懂为什么这不管用。其他人也有类似的问题，因为他们的数据库中的字段比实际列多。或者使用显然不受支持的.csv文件。

这在我要导入的数据库中的其他每个表上都很好，没有任何错误，所以我不明白为什么它在这里不能工作。

Answer 1

终于找到了解决这个问题的办法。结果表明，如果字段中有一个应该有GUID的标准字符串，则不会抛出错误。这可能是因为所述字符串包含在冒号中。

Answer 2

唉哟。回想起我的记忆--我记得有时我不得不在格式文件的末尾放一个返回。(SQL 7，甚至6.5?)因此，可以在格式文件的末尾按enter，或者可选地，确保没有按enter