带有xml格式文件和标识列的bcp导入错误 [英] bcp import error with xml format file and identity column

问题描述

我在SQL Server中创建了一个表，如下所示:

I created a table in SQL server like:

CREATE TABLE [dbo].[    
    [myId] [smallint] IDENTITY(1,1) NOT NULL,
    [name] [nchar](10) NOT NULL,
    [value] [int] NOT NULL,
CONSTRAINT [PK_metadado] PRIMARY KEY CLUSTERED 
(
  [myId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

我想使用xml格式器将文件导入表中.我遇到了一个问题，因为我的表具有"myId".我认为这是bcp中的错误，因为如果我不添加myId列，则导入效果很好.

I want to import a file in my table using xml formater. I got a problem because my table had "myId". I think it's a bug in bcp because, if i don't add myId column, the importation works fine.

文件:

Test      0010000290

Xml格式文件:

<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="CharFixed" LENGTH="10"/>
    <FIELD ID="2" xsi:type="CharFixed" LENGTH="5"/>     
    <FIELD ID="3" xsi:type="CharFixed" LENGTH="5"/> 
</RECORD>
<ROW>
    <COLUMN SOURCE="3" NAME="value" xsi:type="SQLINT" />
    <COLUMN SOURCE="1" NAME="name" xsi:type="SQLCHAR" />
</ROW>
</BCPFORMAT>

输出:

Starting copy...
SQLState = 23000, NativeError = 515
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert the value NULL into column 'value', table 'XXX.dbo.metadata'; column does not allow nulls. INSERT fails.
SQLState = 01000, NativeError = 3621
Warning = [Microsoft][SQL Server Native Client 10.0][SQL Server]The statement has been      terminated.
BCP copy in failed

编辑

@MatthewMartin: 在我的第一种格式中，值"为空. 如果我使用 null 列

@MatthewMartin: The "value" came null whit my first format. It works if i create this strange xml format with null column

<COLUMN SOURCE="2" NAME="null" xsi:type="SQLCHAR" />
<COLUMN SOURCE="1" NAME="name" xsi:type="SQLCHAR" />
<COLUMN SOURCE="3" NAME="value" xsi:type="SQLINT" />    

推荐答案

在一个众所周知的，使用频繁且详尽记录的工具中发现错误的可能性很小.您很有可能没有找到正确的格式说明组合，或者犯了其他错误.

It is extremely unlikely that you have found a bug in a well-known, intensively used and exhaustively documented tool. It is far more likely that you haven't found the correct combination of formatting instructions or are making some other mistake.

话虽如此，(对我而言)尚不清楚您想要实现什么.我最好的理解是您的文件具有3个固定长度的值，您的表具有3列，并且您想要将文件中的2个值复制到表中的2列中，以便最终在name列中以"Test"结尾在值栏中输入100?

Having said that, it's still not entirely clear (to me) what you want to achieve. My best understanding is that your file has 3 fixed-length values, your table has 3 columns, and you want to copy 2 values from the file to 2 columns in the table, so that you end up with 'Test' in the name column and 100 in the value column?

这意味着您要跳过文件中的最后一个值和表中的第一列.请注意文档中的引言:

That would mean you want to skip the last value in the file and the first column in the table. Note this quote from the documentation:

对于XML格式的文件，当您处于 使用bcp命令或BULK直接导入到表中 INSERT语句.但是，您可以导入除最后一列之外的所有列 一张桌子.如果您必须跳过除最后一列以外的任何内容，则必须 创建仅包含列的目标表的视图 包含在数据文件中.然后，您可以从中批量导入数据 文件进入视图.

With an XML format file, you cannot skip a column when you are importing directly into a table by using a bcp command or a BULK INSERT statement. However, you can import into all but the last column of a table. If you have to skip any but the last column, you must create a view of the target table that contains only the columns contained in the data file. Then, you can bulk import data from that file into the view.

使用XML格式文件通过以下方式跳过表列 OPENROWSET(BULK ...)，您必须在其中提供明确的列列表 选择列表以及目标表中的内容，如下所示:

To use an XML format file to skip a table column by using OPENROWSET(BULK...), you have to provide explicit list of columns in the select list and also in the target table, as follows:

插入...从OPENROWSET(BULK ...)中选择

INSERT ... SELECT FROM OPENROWSET(BULK...)

基于所有这些背景，您可以创建视图并使用bcp.exe或仅对表使用OPENROWSET()，我认为这更容易:

Based on all of that background, you can either create a view and use bcp.exe or just use OPENROWSET() with the table, which I think is easier:

表格:

CREATE TABLE [dbo].metadata (
    [myId] [smallint] IDENTITY(1,1) NOT NULL,
    [name] [nchar](10) NOT NULL,
    [value] [int] NOT NULL,
    CONSTRAINT [PK_metadado] PRIMARY KEY CLUSTERED ([myId] ASC)
)

数据文件(行以Windows换行符终止，即CR + LF，请参见样本XML格式文件):

The data file (row terminated with a Windows newline, i.e. CR+LF, see example F under sample XML format files):

Test      0010000290

格式文件:

<?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="CharFixed" LENGTH="10"/>
  <FIELD ID="2" xsi:type="CharFixed" LENGTH="5"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="name" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="2" NAME="value" xsi:type="SQLINT"/>
 </ROW>
</BCPFORMAT>

命令:

insert into dbo.metadata ([name], [value])
select [name], [value]
from openrowset(bulk 'C:\SomeFolder\data.bcp',
    formatfile = 'C:\SomeFolder\format.xml'
    ) dt

最后，还有其他两个要点.请始终提及您使用的SQL Server的版本:许多功能仅在特定的版本/版本中可用.您还应该查看您的列名；我知道您可能只是在此处简单地将它们用作一个简单的示例，但是它们不是很具描述性，VALUE是一个ODBC

Finally, a couple of other small points. Please always mention which version and edition of SQL Server you're using: many features are only available in specific versions/editions. You should also review your column names; I know you may have simply used them for a quick example here, but they are not very descriptive and VALUE is an ODBC reserved keyword that Microsoft recommends should not be used in SQL Server.

这篇关于带有xml格式文件和标识列的bcp导入错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！