[thelist] (sql2k) Bulk Insert chokes on blank line

Tab Alleman Tab.Alleman at MetroGuide.com
Wed Jul 30 12:04:20 CDT 2003


I've got a simple table:

CREATE TABLE [dbo].[tmpallcodes] (
	[OrgID] [char] (9) NULL ,
	[MetroCode] [char] (3) NULL ,
	[KeyName] [varchar] (15) NULL ,
	[KeyCode] [varchar] (127) NULL 
) 

And a .txt file that I import into this table on a regular basis, using
this command:

"BULK INSERT tmpallcodes FROM 'c:\t_all_keycodes.txt'"

Then one day, by some fluke, a blank line got into the middle of my .txt
file.  SQL choked on the BULK INSERT command, giving me:

Microsoft OLE DB Provider for SQL Server: Bulk insert data conversion
error (truncation) for row 5012, column 1 (OrgID).

(Row 5012 is the blank line of course)

My question is:  Is there something I can do, even if it means modifying
my table, so that when there's a blank line in my .txt file, it will
either be imported anyway (nulls and/or defaults for all values) or be
ignored with the rest of the .txt file importing normally?

TIA,
Tab


More information about the thelist mailing list