Bulk Insert Question where all other forums have failed to answer!!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Bulk Insert Question where all other forums have failed to answer!!!

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-05 : 09:12:29

Leigh writes "Microsoft Sql Server Version 7

I have a csv file which contains 3,900,000 rows and am currently using a DTS package to import the data into a table and at present the process takes about 18 minutes.

I want to try and use the Bulk Insert process to transfer the data into the table however the table contains different datatypes mainly varchar, int and nvarchar.

Whenever i try to run the statement i get the following errors:

Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 4 (st_index).
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.

Do you have any suggestions. I have recieved zero reponse from all other forums and you are my last chance if you don't know then i will have to stick with my current dts process.

For info the data is '|' seperated and the first row of the .csv file is field headings.

I hope you can help

Cheers

Leigh"

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-02-05 : 09:53:26

Leigh, Post your code so we can better help you.

In the meantime, did you
1) create a bcp format file?
2) specifiy that Bulk Insert should start importing from the second row (i.e., FIRSTROW = 2)

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2002-02-07 : 15:01:54

here's a tip. Use the bcp command to create your format file for you. Since you already put it into a table via DTS it should create a good format file for you. Unless DTS is modifying the data as it brings it in.
try the following, substitute your values of course.

declare @string as varchar(255)
select @string = 'BCP DB..TABLE FORMAT d:\test -fd:\test.fmt -n -Sserver -Uuser -Ppwd '
exec master..xp_cmdshell @string

Then try the .fmt file it creates.
Cat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources