Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    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
    Code:
    BULK INSERT dbo.Staging_Funds 
    FROM 'D:\TradeAnalysis\ImportedFiles\MutualFunds.txt ' 
    WITH (	FIELDTERMINATOR = '\t',
    	ROWTERMINATOR = '\r\n',
    	TABLOCK)
    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.

    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.
    Code:
    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.
    I do, they are.

    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)
    aka "Paul"
    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Native is the easy way out.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Yeah, but then I get the error about the line being too long. I'll play with it more on Monday. For the time being I just made the bit columns varchar. THAT is the easy way out

    It somehow just doesn't "feel right" though.
    aka "Paul"
    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

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you BCP the data out using Native, then BCP it back in using Native, you'd better not get any error! That would be a really, really bad sign.

    -PatP

  5. #5
    Join Date
    Jun 2004
    Posts
    50
    OK, I'm just putting this out there...
    Can you use DTS to change 'true' into 1 and 'false' into 0? I am not as savvy as our resident curmudgeon on these matters, but it's something I'd investigate.

    hth
    Monk
    The person who confesses ignorance shows it once; the person who conceals it shows it many times.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •