bulk insert problem - SQL Server Forums

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 problem

Author

Topic

jyothinair
Starting Member

12 Posts

Posted - 2008-03-24 : 05:03:28

i am intending to copy a notepad file into my sql table. How am i supposed to insert the content into the table if the columns are more than the rows in the notepad file.
My notepad file is basically a route description file
Step1: Start from from Chruchgate
Step2..........
Step3...............
If i have 5 columns in my table, how to insert these three rows of notepad file using bulk insert.
Please notify, if there is any method to insert a folders content into a table.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-24 : 11:03:39

Check 'Bulk-insert' in BOL.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-24 : 21:44:09

And set other columns in the table to allow null.

jyothinair
Starting Member

12 Posts

Posted - 2008-03-25 : 01:09:41

can you tell how to use bulk insert in bol.we have tried using
bulk
insert into table name
from"path of the file"
with(fieldterminator="\n")
go
we are not getting our problem solved .please tell us if there is any bug in the bulk insert statement

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-25 : 23:00:34

Did you get any error? Tried with format file?

jyothinair
Starting Member

12 Posts

Posted - 2008-04-02 : 15:27:24

I used format file but still i am getting the same error.
The format file used was
bcp hello.dbo.TmpStList format null -c -f d:\MyTestDefault2-f-c.Fmt -t,-r\n -T
The above command did not give any error.
But after trying bulk insert query i got the same error

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

please help!!!!!!!!!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-02 : 21:44:12

Is data file on sql server's local disk?

jyothinair
Starting Member

12 Posts

Posted - 2008-04-03 : 10:52:21

Yes, My OS as well as SQL SERVER is installed in D:.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-03 : 22:01:26

>> with(fieldterminator="\n")

What's column delimter in data file?

jyothinair
Starting Member

12 Posts

Posted - 2008-04-04 : 14:39:27

The column delimeter is the next line of the data file

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-04 : 23:41:40

Are you sure? That sounds row delimiter.

jyothinair
Starting Member

12 Posts

Posted - 2008-04-05 : 10:11:40

Yes I m sure. The row delimiter would be the next notepad file in my folder . I m not sure how to express it as my row delimeter.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-05 : 21:32:38

Each file contains one row of data?

jyothinair
Starting Member

12 Posts

Posted - 2008-04-06 : 04:17:36

I ll explain my problem again. I have got notepad files that store the driving directions between two corresponding locations. I have a table having columns source, destination, step1,step2.......step30.
For eg source=mulund destination=bhandup
Step1: Cross the mulund bridge and reach mulund east bus stop
Step2: Turn hard left on L.B.S Marg
The notepad file would have the source, destination and the corresponding steps. The next notepad file would have directions for another location.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-06 : 16:36:58

Ok, each file does contain one row of data. You have to ensure data file has proper column and row delimiters, and you specify them correctly in bulk insert statement. By the way, row delimiter can't be another file.

jyothinair
Starting Member

12 Posts

Posted - 2008-04-07 : 12:57:50

Could you suggest any method for doing this operation using bulk insert?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-08 : 23:44:24

Find out correct column delimiter and row delimiter in those files.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources