Unanswered: Quick Bulk Insert question
On a Friday afternoon, no less.
Anyway, in a nutshell, I have a flat file (tab-delimited, row terminator is ":0D0A" (or '\r\n'). I want to bulk import it into a table that is defined EXACTLY like the table (on a remote system) that the data comes from (I stole the remote tables DDL by scripting the table definition).
Some of the columns in the table are BIT data types.
In the flat (text) file that I get, those column values are present as the literal words "true" and "false".
When I do the bulk insert
it fails saying there is a data type mismatch between the file and the local table column that corresponds to the first BIT data type in the file.
BULK INSERT dbo.Staging_Funds
FROM 'D:\TradeAnalysis\ImportedFiles\MutualFunds.txt '
WITH ( FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\r\n',
Thinking (uh-oh) there has got to be an easier way, I modify my bulk insert and add a DATAFILETYPE = 'native' to the bulk insert. This gets me past the error (or not) but results in a DIFFERENT error.
I do, they are.
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
Is this an either/or situation? Meaning, if I use "native" format, does it ignore the fieldterminator for some reason?
I am tempted to modify my staging table to just pull in the flag (BIT) columns as char(5) columns, and be done with it (we don't use the flag bits at present anyway) but there is also a part of me that says this should be doable, for crissake.
Any quick guidance or thoughts (s'OK, the thoughts can be slower...it's Friday, I know)
Non est ei similis.
I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them