Post reply

Query regarding BULK INSERT

  • July 7, 2008 at 11:31 pm

    #184303

    Hi All

    I have a query regarding bulk insert, I have one txt file which contains Date and Time field speprated with #. I want to insert these two fields in single column by concating the value. Also i need to calculate the the value of one more field from the value of one column in txt file.

    How can i do this?

    Cheers

  • July 9, 2008 at 4:11 am

    #839962

    TO deal with the separator, you need to create (and use) a format file with your BULK INSERT.

    Any number of web articles will help you out with this - sorry, but I haven't got any of my own examples to hand.

    Format Files...

    http://msdn.microsoft.com/en-us/library/ms191479.aspx

    you'll need "8.0" instead of 9.0 at the top for SQL 2000

  • July 9, 2008 at 4:15 am

    #839963

    Oh and consider bulk inserting the data "as-is" i.e. in the 2 seperate fields into a staging table - then concatenating them when you load into the actual table(s).

    It's also handy to write yourself a usp_BulkInsert stored proc which takes the file name, the format file name, the databse table name etc. etc. for future reuse...

  • July 9, 2008 at 7:34 pm

    #840587

    Make your life easy... import the date#time into a single column and replace the # sign with a space in the process of converting it to a real date/time.

    --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 10, 2008 at 2:06 am

    #840677

    yeah , Jeff's suggestion would be the easiest way.

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

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