Post reply

bulk insert

  • July 13, 2008 at 7:49 pm

    #190668

    Hi Folks,

    is there any way to automatically truncate data that is too big for a field in SQL Server using T-SQL

    Example.

    I have a table that has 1 field - email char(30). I am trying to bulk insert from a csv file. Some of the emails are bigger than 30 chars and need to be truncated. Would like T-SQL to handle if possible

    Please help!

  • July 14, 2008 at 5:19 am

    #842302

    Not directly, you "could" use SSIS and truncate the required columns in the SIS package, but I would suggest a better approach is to extend the column in the db to an appropriate length. My understanding is that varchar(240) should be adequate for email addresses.

    Mike John

  • July 14, 2008 at 6:32 am

    #842329

    bodhilove (7/13/2008)

    I have a table that has 1 field - email char(30). I am trying to bulk insert from a csv file. Some of the emails are bigger than 30 chars and need to be truncated.

    Why would you do that? An email address isn't even worth storing unless you have the whole thing. :blink:

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 14, 2008 at 5:12 pm

    #842757

    The emails in the file are padded with spaces making it too big for field

  • July 14, 2008 at 5:22 pm

    #842760

    I think you're importing directly to the final table... why not import into a staging table and then you can do some validations and trimming before the data goes into the final table. Either that, or you can build a BCP format file to shortcut the column at 30 characters.

    The thing I'm concerned about is... what are you going to do when you run across an email address like JohnathanGFarnsworth@Ameritech.net if you're limited to 30 characters? 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 14, 2008 at 8:33 pm

    #842775

    I'll look into bcp .. thanks Jeff:)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply