SQL 使用 BULK INSERT 时忽略某些列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14711814/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Ignore certain columns when using BULK INSERT
提问by wootscootinboogie
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?
插入工作正常，但插入的结果使 field4 看起来像 field3,field4，因此字段 3 实际上只是连接到 field4。我正在使用的平面文件是几个演出，不能轻易修改。有没有办法使用批量插入但忽略在 create table 语句中未声明的列？
采纳答案by Janine Rawnsley
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/ms178129.aspx
http://msdn.microsoft.com/en-gb/library/ms179250.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. ;)
或者，如果您想要一种稍微厚脸皮的方式，只需将其全部导入并在之后删除一列。;)
回答by Charles Okwuagwu
The easiest way is to create a view that has just the columns you require.
最简单的方法是创建一个仅包含您需要的列的视图。
Then bulk insert into that view.
然后批量插入到该视图中。
Example:
例子：
create table people (name varchar(20) not null, dob date null, sex char(1) null)
--If you are importing only name from list of names in names.txt
create view vwNames as
select name from people
bulk insert 'names.txt'
回答by user2418601
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 列并删除你不想要的列
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]
