sql-server - 为什么批量插入的错误文件不起作用？

Question

感谢您的反馈。

这是我为强制错误而制作的表格。但除非所有列都是 255，否则它不会输入任何内容。这些接近正确的列大小‌。这是临时表。我希望创建一个错误文件，但没有创建任何文件。

我得到的信息是:

Msg 8152, Level 16, State 14, Line 3 String or binary data would be truncated. The statement has been terminated. ‌

drop TABLE VendorUpc  
CREATE TABLE [dbo].[VendorUpc](
    [ID] [bigint] NOT NULL Identity(1,1) Primary key,
    [VendorNumber] [varchar](8) NULL,
    [VendorName] [varchar](50) NULL,
    [ItemNumber] [varchar](50) NULL,
    [ProductDescription] [varchar](900) NULL, 
    [UOM] [varchar](255) NULL, ---?
    [UPC] [varchar](255) NULL ---? 
) ON [PRIMARY] GO

select * from dbo.VendorUpc

这是批量插入脚本
‌

truncate table VendorUpc

insert into VendorUpcg
(
    -- ID - This column value is auto-generated
    VendorNumber,
    VendorName,
    ItemNumber,
    ProductDescription,
    UOM,
    UPC    
)
select
    b.VendorNumber,
    b.VendorName,
    b.ItemNumber,
    b.ProductDescription,
    b.UOMs,
    b.UPCs    
from openrowset
(
    bulk 'F:\Data\UPC\Master_File.txt',
    formatfile = 'F:\Data\UPC\Format.xml', 
    errorfile = 'F:\Data\UPC\bulk_insert_BadData.txt', 
    firstrow = 1
) as b

这是格式文件:

<?xml version="1.0" encoding="utf-8"?>
<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='|' />
  <FIELD ID ="2" xsi:type="CharTerm" TERMINATOR='|' />
  <FIELD ID ="3" xsi:type="CharTerm" TERMINATOR='|'/>
  <FIELD ID ="4" xsi:type="CharTerm" TERMINATOR='|'/>
  <FIELD ID ="5" xsi:type="CharTerm" TERMINATOR='|'/>
  <FIELD ID ="6" xsi:type="CharTerm" TERMINATOR='\n'/> 
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="VendorNumber" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="VendorName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="ItemNumber" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="ProductDescription" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="UOMs" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="6" NAME="UPCs" xsi:type="SQLVARYCHAR"/>
</ROW> 
</BCPFORMAT> ‌

‌‌‌‌
样本数据:

WT1900|TrueWater|600|"3/4"" Anti-siphon body less union"|Piece|843687114345
WT1900|TrueWater|601|"3/4"" Anti-siphon body with union"|Piece|843687114383
WT1900|TrueWater|602|"1"" Anti-siphon body less union"|Piece|843687114369
WT1900|TrueWater|603|"1"" Anti-siphon body with union"|Piece|843687114406
SUN333|Sunny Bay|604|"3/4"" AVB only"|Piece|843687122029
SUN333|Sunny Bay|605|"1"" AVB only"|Piece|843687122036
SUN333|Sunny Bay|606|"1-1/4"" AV & AVB"|Piece|843687122043
SUN333|Sunny Bay|607|"1-1/2"" AV & AVB"|Piece|843687122050
HD9319|Home Decor|608|"2"" AV & AVB"|Piece|843687122067
HD9319|Home Decor|609|"3/4"" AVU only"|Piece|843687122074

Answer 1

您正在使用引用的文本。例如

"3/4"" Anti-siphon body less union"

批量复制不能正确处理。