问题描述
我在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格式文件通过以下方式跳过表列 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导入错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！