BULK INSERT : need to load into selected columns of the table

Question

Post reply

BULK INSERT : need to load into selected columns of the table

Oracle_91

SSChampion

Points: 14548
More actions
January 6, 2010 at 2:56 am

#99981

Hi,
I have a problem with BULK INSERT.
Need to insert into select columns in the target table!
create table sample
(
fname varchar(200),
lname varchar(200),
email varchar(200)
)
--Source file content
C:\TEST.TXT
Kelly,Reynold,kelly@reynold.com
John,Smith,bill@smith.com
Sara,Parker,sara@parker.com
BULK INSERT sample FROM 'c:\test.txt' WITH (FIELDTERMINATOR = ',')
-- Here am dropping the table and recreated the table with extra column "dob" but my flat file remains the same and
-- imp note is i can insert NULL's into the extra column.
-- I tried to load data as below mentioning explicit columns. But am getting the below error .
/*
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (dob).
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
*/
drop table sample
create table sample
(
fname varchar(200),
lname varchar(200),
email varchar(200),
dob datetime
)
BULK INSERT sample(fname,lname,email) FROM 'c:\test.txt' WITH (FIELDTERMINATOR = ',')
C:\TEST.TXT
Kelly,Reynold,kelly@reynold.com
John,Smith,bill@smith.com
Sara,Parker,sara@parker.com
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

You need to provide full rows (all columns and in the exact column order ! ) if you want to use bulk insert !

Workaround:

Create a view that contains only the columns you are going to load and blukinsert on that view.

-- TEST IT -- TEST IT -- TEST IT -- TEST IT --

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

LutzM SSC Guru Points: 107049 More actions · Answer 2

You would need a format file to exclude the dob column.

See BOL for details.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 3

I used the following format file and it worked without any problem:

Please note the way I defined the dob column to be ignored.

9.0
4
1 SQLCHAR 0 200 "," 1 fname Latin1_General_CI_AS
2 SQLCHAR 0 200 "," 2 lname Latin1_General_CI_AS
3 SQLCHAR 0 200 "\r" 3 email Latin1_General_CI_AS
4 SQLDATETIME 0 0 "" 0 dob ""

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Oracle_91 SSChampion Points: 14548 More actions · Answer 4

Thanks All,

i have used the format file. it worked out.

9.0

3

1 SQLCHAR 0 100 ","1 fname ""

2 SQLCHAR 0 100 "," 2 lname ""

3 SQLCHAR 0 100 "\r" 3 email SQL_Latin1_General_CP1_CI_AS