使用BULK INSERT时忽略某些列 [英] Ignore certain columns when using BULK INSERT

问题描述

我有一个逗号分隔的文本文件，其结构为

I have a comma delimited text file with the structure

field1   field2   field3   field4
1        2        3        4

我编写了以下脚本来批量插入文本文件，但我想省略第3列

I wrote the following script to bulk insert the text file, but I wanted to leave out column 3

create table test (field1 varchar(50),field2 varchar(50),field4 varchar(50))
go
bulk insert test
from 'c:\myFilePath'
with 
(fieldterminator=',',
rowterminator='\n'
)

插入效果很好，但是插入的结果使field4看起来像
field3， field4，因此字段3实际上只是连接到field4上。我正在使用的平面文件有几场演出，无法轻松修改。有没有办法使用大容量插入，但可以忽略大容量插入而不在create table语句中声明的列？

The insert worked fine, but the results of the insert made field4 look like field3,field4, so the field 3 was actually just concatenated onto field4. The flat files I'm working with are several gigs and can't be easily modified. Is there a way to use bulk insert but have it ignore the columns that aren't declared in the create table statement?

推荐答案

您可以使用格式文件来执行此操作：

You can use a format file to do this:

http://msdn.microsoft.com/en-gb/library/ms178129.aspx

http://msdn.microsoft.com/en-gb/library/ms179250.aspx

或者，如果您想使用更宽容的方法，只需将其全部导入，然后再删除一列即可。 ;）

Or if you want a slightly cheekier way, just import it all and drop a column afterwards. ;)

这篇关于使用BULK INSERT时忽略某些列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！