BULK INSERT not inserting all records

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 not inserting all records

Author

Topic

samirehman
Starting Member

2 Posts

Posted - 2004-03-22 : 06:17:25

HI,
I m using BULK INSERT to load data from a text file which contains 201664 rows ( records )... but BULK INSERT is inserting only 100823 records in data base... following is the cammand

BULK INSERT [lagacy_data]
FROM 'D:\Files\lagacy_data.txt'
WITH
(
KEEPNULLS ,
ROWTERMINATOR = '\n',
FIELDTERMINATOR = '\t'
)

The lagacy_Data table has 40 columns with VARCHAR(255) datatype..

Wht I m missing ... any suggestion ??
---------------------------------------------------------------------
--------------------------------------------------------------------------------

I hve tried both the ways by reducing the size (less than 8k) and by mentioning the MAXERRORS clause...

BULK INSERT [lagacy_data]
FROM 'D:\lagacy_data.txt'
WITH
(
KEEPNULLS ,
ROWTERMINATOR = '\n',
FIELDTERMINATOR = '\t',
MAXERRORS = 20
)

(100823 row(s) affected) -- Aprox half of the total records
The process ends without giving any error

The file is successfully imported by IMPORT EXPORT WIZARD....201647 records .....
Why BULK INSERT is not loading all the records .....

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-22 : 06:48:04

Without specifying the MAXERRORS option, the import may be being cancelled after 10 errors. Verify this by specifying the MAXERRORS option with a higher limit.

Raymond

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-22 : 09:13:39

40 * 255?

Row length is too long...

Like Ray said...

You've encountered errors....what are they?

Brett

8-)

samirehman
Starting Member

2 Posts

Posted - 2004-03-22 : 20:52:56

I hve tried both the ways by reducing the size (less than 8k) and by mentioning the MAXERRORS clause...

BULK INSERT [lagacy_data]
FROM 'D:\lagacy_data.txt'
WITH
(
KEEPNULLS ,
ROWTERMINATOR = '\n',
FIELDTERMINATOR = '\t',
MAXERRORS = 20
)

(100823 row(s) affected) -- Aprox half of the total records
The process ends without giving any error

The file is successfully imported by IMPORT EXPORT WIZARD....201647 records .....
Why BULK INSERT is not loading all the records .....

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-22 : 21:32:00

Look at the format of the rows.
If a row does not complete the record it may take the next line to complete it. If the data is valid for the fields then it won't give an error.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jairo777
Starting Member

1 Post

Posted - 2010-07-13 : 15:43:28

Ensure number of fields in database table match number of fields in text file. More fields in the database table cause text file data to wrap into the extra database fields making it seem that only half the text file data was inserted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources