By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,652 Members | 1,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,652 IT Pros & Developers. It's quick & easy.

BULK INSERT not loading all rows from a text file.

P: 1
I have written a stored procedure that is used to load data into a SQL Server 2000 and 2005 databases from some 40ish different text files. One of the text files is loading all of the rows apart from the last one. No error message is issued when this happens.
The command I am using to load the data is as follows:

Expand|Select|Wrap|Line Numbers
  1. BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt', MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc\Basicdata.bad') 
(I understand that the ERRORFILE option only works in SQL Server 2005, but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of data in the last row.

Expand|Select|Wrap|Line Numbers
  1. 49708    60646    2006-11-15    1    CITY SALES TAX    1    00    00    02    02    
  2. 49709    60647    1990-01-01    1    GROSS RECEIPTS TAX    2    03    01    02    02<tab>
Unless the last tab is manually inserted, the last row does not get loaded to the database, and no error message is issued. If the tab is inserted all of the data is loaded correctly. However, the text files come from a third party so I cannot ensure that this problem will not occur again.

The .fmt file used to load the data is as follows:

Expand|Select|Wrap|Line Numbers
  1. 8.0
  2. 11
  3. 1       SQLCHAR       0       12      "\t"                      1     BasicDataID            ""
  4. 2       SQLCHAR       0       12      "\t"                      2     BasicTaxOverrideID     ""
  5. 3       SQLCHAR       0       24      "\t"                      3     LegalEffectiveDate     ""
  6. 4        SQLCHAR       0       12      "\t"                      4     ReleaseNo              ""
  7. 5       SQLCHAR       0       50      "\t"                      5     BasicDataDesc          SQL_Latin1_General_CP1_CI_AS
  8. 6       SQLCHAR       0       1       "\t"                      6     PassFlag               SQL_Latin1_General_CP1_CI_AS
  9. 7       SQLCHAR       0       2       "\t"                      7     PassType               SQL_Latin1_General_CP1_CI_AS
  10. 8       SQLCHAR       0       2       "\t"                      8     BaseType               SQL_Latin1_General_CP1_CI_AS
  11. 9       SQLCHAR       0       2       "\t"                      9     DateFlag               SQL_Latin1_General_CP1_CI_AS
  12. 10      SQLCHAR       0       2       "\t"                      10    RoundingLevel          SQL_Latin1_General_CP1_CI_AS
  13. 11      SQLCHAR       0       8       "\r\n"                    11    TaxGroupingCode        SQL_Latin1_General_CP1_CI_AS
  14.  
My question is, is there any way to trigger an error message to denote that (in my example) the last row did not get loaded successfully? Is there a parameter associated with the BULK INSERT command that I am not currently using that could help with this problem?
Jun 22 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.