无法使用XML模型批量插入 [英] Failing to bulk insert with XML model

问题描述

我正在尝试使用大容量插入将大量带有元数据的二进制文件插入SQL Server表中。我只想指定要使用的列的子集（最重要的是，PK是 UniqueIdentifier ，我希望SQL Server生成此字段）。因此，我必须使用模型文件。

I am trying to use BULK INSERT to insert a large amount of binary files with metadata into a SQL Server table. I want to only specify a subset of the columns to use (most importantly, the PK is a UniqueIdentifier and I want SQL Server to generate this field). And so I must use a model file.

不使用模型文件（为所有列输入数据）对此文件可以正常工作：

Not using a model file (entering data for all columns) works fine with this file:

00000000-F436-49D0-B5A9-02DAB2E03F45, B, , , , JVBERio4MjEzNQ0KJSVFT0Y=, 109754, 2017-12-14 14:53:23, 2017-12-14 14:53:23, number, name

此语句：

BULK INSERT Documents
FROM 'c:\temp\testdata.txt'
WITH
(
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n',
  ROWS_PER_BATCH = 1000, 
  FIRSTROW = 1,
  TABLOCK
)

但是当我尝试使用模型文件时，我遇到了两个不同的问题。首先，当使用指定所有列的模型文件时：

But when I try to use a model file, I have run into two different problems. Firstly, when using a model file that specifies all columns:

<?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="NativePrefix" PREFIX_LENGTH="1"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="6" xsi:type="NCharPrefix" PREFIX_LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="7" xsi:type="NativeFixed" LENGTH="4"/>
  <FIELD ID="8" xsi:type="NativeFixed" LENGTH="8"/>
  <FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
  <FIELD ID="10" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="11" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="DocumentId" xsi:type="SQLUNIQUEID"/>
  <COLUMN SOURCE="2" NAME="DocumentType" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="CompanyCode" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="CustomerNumber" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="OrderNumber" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="6" NAME="Data" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="7" NAME="Size" xsi:type="SQLINT"/>
  <COLUMN SOURCE="8" NAME="Created" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="9" NAME="Modified" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="10" NAME="InvoiceNumber" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="11" NAME="Title" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

和以下语句：

BULK INSERT Documents   
   FROM 'c:\Temp\testiness.txt'   
   WITH
   (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n',
      ROWS_PER_BATCH = 1000, 
      FIRSTROW = 1,
      TABLOCK,
      FORMATFILE = 'c:\Temp\modeltest.xml'
   );  
GO 

我收到此错误：

消息4866，级别16，状态7，第1行

批量装入失败。数据文件中该列对于第1行，第2列而言太长。请验证是否正确指定了字段终止符和行终止符。

Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 2. Verify that the field terminator and row terminator are specified correctly.

Msg 7399，级别16，状态1，行1

链接服务器（null）的OLE DB访问接口 BULK报告了一个错误。提供程序没有提供有关该错误的任何信息。

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

消息7330，级别16，状态2，行1

无法从OLE中获取行链接服务器（null）的数据库提供程序 BULK。`

Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".`

并且在使用此简化模型文件（以及带有更少的值）：

And when using this reduced model file (and atext file with fewer values):

<?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="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="NCharPrefix" PREFIX_LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="6" xsi:type="NativeFixed" LENGTH="4"/>
  <FIELD ID="7" xsi:type="NativeFixed" LENGTH="8"/>
  <FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
  <FIELD ID="9" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="DocumentType" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2" NAME="CompanyCode" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="CustomerNumber" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="OrderNumber" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="Data" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="6" NAME="Size" xsi:type="SQLINT"/>
  <COLUMN SOURCE="7" NAME="Created" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="8" NAME="Modified" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="9" NAME="Title" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

我得到：

消息257，级别16，状态3，第1行

不允许从数据类型datetime到int的隐式转换。使用CONVERT函数运行此查询。

Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

推荐答案

我建议导入此数据进入容忍的登台表，然后从那里继续。只是一个观察结果：

I'd suggest to import this data into a tolerant staging table and continue from there. Just one observation:

<FIELD ID="7" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="9" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100"> 

对应于

<COLUMN SOURCE="7" NAME="Size" xsi:type="SQLINT"/>
<COLUMN SOURCE="8" NAME="Created" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="9" NAME="Modified" xsi:type="SQLDATETIME"/>

Nr 7似乎是 INT （虽然8字节是很多），而8和9则指向日期值（按列的名称和类型），而< FIELD> 的定义不一致。

Nr 7 seems to be an INT (8 byte is to much though), while 8 and 9 point to date value (by the column's names and types), while the <FIELD> definition is not consistent.

这篇关于无法使用XML模型批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！