简单的SQL批量插入无法正常工作 [英] Simple SQL Bulk Insert not working

问题描述

我正在尝试创建一个简单的Bulk Insert命令，以将固定宽度的文本文件导入到表中.完成这项工作后，我将在此基础上进行扩展以使我更复杂的导入工作.

I'm trying to create a simple Bulk Insert command to import a fixed width text file into a table. Once I have this working I'll then expand on it to get my more complex import working.

我当前收到错误消息...

I'm currently receiving the error...

4866级消息，状态16，状态7，第1行
批量加载失败.数据文件中第1行第1列的列太长.请验证是否正确指定了字段终止符和行终止符.

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 1. Verify that the field terminator and row terminator are specified correctly.

很显然，我已经检查了文件中的终止符.对于测试数据，我只是在记事本中键入了3行文本文件.在这个阶段，我只是想每行导入一列.我已经填充了前两行，所以每行长18个字符.

Obviously I have checked the terminator in the file. For test data I just typed a 3 line text file in Notepad. At this stage I'm just trying to import one column per line. I have padded the first two lines so each one is 18 characters long.

Test.txt

This is line one  
This is line two  
This is line three

当我在Notepad ++中查看文件并打开所有字符时，我在每行末尾看到CRLF，在文件末尾没有空白行.

When I view the file in Notepad++ and turn on all characaters I see CRLF on the end of each line and no blank lines at the end of the file.

这是我正在使用的SQL:

This is the SQL I'm using:

USE [Strata]
GO
drop table VJR_Bulk_Staging

Create Table [dbo].[VJR_Bulk_Staging](
[rowid] int Identity(1,1) Primary Key,
[raw] [varchar](18) not null)

GO
Bulk Insert [VJR_Bulk_Staging] 
From 'c:\temp\aba\test.txt'
with (FormatFile='c:\temp\aba\test2.xml')

这是格式XML文件.我尝试了几种变体.这是使用BCP命令创建的.

Here is the format XML file. I have tried several variations. This one was created using the BCP command.

bcp strata.dbo.vjr_bulk_staging format nul -f test2.xml -x -n -T -S Server\Instance

这为我的rowid列创建了一条记录和一个行条目，我认为这是一个问题，因为这是一个身份字段，所以我将其删除.

This created a record and a row entry for my rowid column which I thought was a problem as that is an identity field, so I removed it.

<?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="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="18" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="raw" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

我正在SQL Server 2008 R2 Express上进行测试.

I'm testing on SQL Server 2008 R2 Express.

有什么想法我要去哪里吗?

Any ideas where I'm going wrong?

推荐答案

这有效.

9.0  
1
1 SQLCHAR 0 18 "\r\n" 2 raw SQL_Latin1_General_CP1_CI_AS

或简单地

9.0  
1
1 SQLCHAR "" "" "\r\n" 2 "" ""

---

选项2:XML格式文件

丑陋的解决方法

<?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="CharTerm" TERMINATOR="\r" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\n"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="2" xsi:type="SQLINT"/>
  <COLUMN SOURCE="1" xsi:type="SQLCHAR"/>  
 </ROW>
</BCPFORMAT>

P.s.
在我看来，XML格式文件的设计中存在错误.
与Non-XML格式的文件不同，没有选项来指示已加载列的位置(并且名称仅是为了脚本的清晰起见，它们没有实际含义).
文档中的XML示例不不起作用
使用格式文件跳过表列(SQL Server)

P.s.
It seems to me that there is a bug in the design of the XML format file.
Unlike the Non-XML format file, there is no option to indicate the position of the loaded column (and the names are just for the clarity of the scripts, they have no real meanning).
The XML example in the documentation does not work
Use a Format File to Skip a Table Column (SQL Server)

这篇关于简单的SQL批量插入无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！