BULK INSERT : need to load into selected columns of the table
-
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
-
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
-
You would need a format file to exclude the dob column.
See BOL for details.
-
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 ""
-
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
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply