The command I am using to load the data is as follows:
Expand|Select|Wrap|Line Numbers
- 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')
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
- 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02 02
- 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01 02 02<tab>
The .fmt file used to load the data is as follows:
Expand|Select|Wrap|Line Numbers
- 8.0
- 11
- 1 SQLCHAR 0 12 "\t" 1 BasicDataID ""
- 2 SQLCHAR 0 12 "\t" 2 BasicTaxOverrideID ""
- 3 SQLCHAR 0 24 "\t" 3 LegalEffectiveDate ""
- 4 SQLCHAR 0 12 "\t" 4 ReleaseNo ""
- 5 SQLCHAR 0 50 "\t" 5 BasicDataDesc SQL_Latin1_General_CP1_CI_AS
- 6 SQLCHAR 0 1 "\t" 6 PassFlag SQL_Latin1_General_CP1_CI_AS
- 7 SQLCHAR 0 2 "\t" 7 PassType SQL_Latin1_General_CP1_CI_AS
- 8 SQLCHAR 0 2 "\t" 8 BaseType SQL_Latin1_General_CP1_CI_AS
- 9 SQLCHAR 0 2 "\t" 9 DateFlag SQL_Latin1_General_CP1_CI_AS
- 10 SQLCHAR 0 2 "\t" 10 RoundingLevel SQL_Latin1_General_CP1_CI_AS
- 11 SQLCHAR 0 8 "\r\n" 11 TaxGroupingCode SQL_Latin1_General_CP1_CI_AS