在SQL Server批量插入中，如何对字段和行终止符使用较高的ASCII字符 [英] In SQL Server bulk insert, how do I use higher ASCII characters for Field and Row terminators
问题描述
我有一个可在SQL Server 2000上运行的批量插入件，该插入件试图在SQL Server 2008 R2上运行，但未如我所愿.我已经使用以下命令成功将这些大容量插入语句运行到SQL 2000中:
I have a bulk insert that works on SQL Server 2000 that I'm trying to run on SQL Server 2008 R2, but it's not working as I had hoped. I've been successfully running these bulk inserts into SQL 2000 with the following:
格式化文件
8.0
9
1 SQLCHAR 0 0 "ù" 1 Col1 ""
2 SQLCHAR 0 0 "ù" 2 Col2 ""
3 SQLCHAR 0 0 "ù" 3 Col3 ""
4 SQLCHAR 0 0 "ù" 4 Col4 ""
5 SQLCHAR 0 0 "ù" 5 Col5 ""
6 SQLCHAR 0 0 "ú" 6 Col6 ""
7 SQLCHAR 0 0 "" 0 Col7 ""
8 SQLCHAR 0 0 "" 0 Col8 ""
9 SQLCHAR 0 0 "" 0 Col9 ""
数据文件:
101ù110115100ùC02BCD72-083E-46EE-AA68-848F2F36DB4Dù0ù1ùCú
批量插入命令:
bulk insert Database1.dbo.Table1
from 'C:\DataFile.dat'
with
(
formatfile = 'C:\FormatFile.fmt'
, tablock
, check_constraints
, maxerrors = 0
)
现在我在SQL 2008 R2机器上运行，出现以下错误:
Now that I'm running on a SQL 2008 R2 box, I'm getting the following error:
Bulk load: An unexpected end of file was encountered in the data file.
如果我将字段终止符从ascii 249(ù)更改为逗号(，)，并将行终止符从ascii 250(ú)更改为分号(;)，则所有操作都会运行.但是，这并不是一个真正的选择(数据中肯定会包含这些字符)，我不想为我的定界符选择诸如！@#$％^& *()之类的任意字符串(必须进行更多编辑)这样的代码.
If I change my field terminators from ascii 249 (ù) to commas (,) and change my row terminators from ascii 250 (ú) to semi-colons (;), everything will run. However, this isn't really an option (the data will certainly have those characters in it) and I'd rather not pick some arbitrary string like !@#$%^&*() for my delimiters (have to edit more code that way).
我尝试了代码页，数据文件类型，排序规则，sql兼容级别和格式文件版本的几种组合，但无济于事(不是我有专门知识来知道所有这些元素将如何交互以更改此处的任何内容).批量插入MSDN文档的各个部分都引用了有关大于127或小于32的ascii字符的特殊规则，但是我不太清楚这将如何影响定界符.
I've tried a few combinations of codepage, datafiletype, collation, sql compat level and format file version, but to no avail (not that I have the expertise to know how all those would interact to change anything here). Various parts of the bulk insert MSDN docs refer to special rules concerning ascii characters greater than 127 or less than 32, but I can't quite make out how that would affect the delimiters.
我可以做些什么，以使代码尽可能少但可以使其在我的新服务器上运行?
What can I do to touch as little code as possible but make it run on my new server?
更新(解决方案)
由于@Adam Wenger的评论，我找到了解决方案.为了处理数据中的扩展ASCII 字符，我不再使用格式文件，而是将大容量插入数据文件作为Unicode(不是ANSI)写入文件系统(即使我的数据中确实没有Unicode字符).这是我的新批量插入语句(注意'widechar'):
Thanks to @Adam Wenger's comment, I have found a solution. To deal with having extended ASCII characters in my data, I am no longer using a format file and am writing the bulk insert data file as unicode (not ANSI) to the filesystem (even though there're really no unicode chars in my data). Here is my new bulk insert statement (notice 'widechar'):
bulk insert Database1.dbo.Table1
from 'C:\DataFile.dat'
with (
check_constraints
, datafiletype = 'widechar'
, fieldterminator = 'ù'
, maxerrors = 0
, rowterminator = 'ú'
, tablock
)
无论我尝试了什么，我都无法使用扩展ASCII字符(高于127)来使用格式文件.我只是简单地摆脱了格式文件，现在将其他字段分隔符放入数据文件中，以表示不导入的列(这些列具有默认值).
I could not get a format file to work with extended ASCII characters (above 127) no matter what I tried. I simply got rid of the format file and now put the additional field delimiters in my data file to represent the columns that I'm not importing (I have defaults on these columns).
推荐答案
在WITH块中指定DATAFILETYPE='widechar'应该可以通过在其中指定"widechar"字段和行终止符来消除使用格式文件的需要BULK INSERT的WITH.我引用了 MSDN文章有关用于导入数据的Unicode字符格式. >
Specifying DATAFILETYPE='widechar' inside your WITH block should remove your need to use the format file by being able to specify the "widechar" field and row terminators in the WITH of the BULK INSERT as well. I referenced this MSDN article on unicode character format for importing data.
BULK INSERT Database1.dbo.Table1
FROM 'C:\DataFile.dat'
WITH ( TABLOCK
, CHECK_CONSTRAINTS
, MAXERRORS = 0
, DATAFILETYPE = 'widechar'
, FIELDTERMINATOR = 'ù'
, ROWTERMINATOR = 'ú'
)
这篇关于在SQL Server批量插入中，如何对字段和行终止符使用较高的ASCII字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
