使用批量插入时忽略某些列。

I have a comma delimited text file with the structure

我有一个带结构的逗号分隔的文本文件

field1   field2   field3   field4
1        2        3        4

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

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

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

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?

insert工作的很好，但是插入的结果使field4看起来像field3,field4，所以字段3实际上只是连接到field4。我正在处理的平面文件有好几个版本，不能轻易修改。是否有一种方法可以使用批量插入，但它是否忽略了create table语句中未声明的列?

3 个解决方案

#1

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. ;)

或者，如果你想要更厚颜无耻的方式，只需要导入所有内容，然后删除一列。,)

#2

The easiest way is to create a view that has just the columns you require.

最简单的方法是创建一个只有所需列的视图。

Then bulk insert into that view.

然后批量插入到视图中。

#3

you cant ignore a field while doing bulk insert , insted of doing that .. Load all 4 column and drop the colum which you dont want

在进行大容量插入时，不能忽略一个字段。加载所有4列，并删除你不想要的colum

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

ALTER TABLE test DROP column [field3]

#1