Post reply

BULK INSERT with a format file

  • March 5, 2015 at 5:06 am

    #295019

    Hi all,

    I have to perform a bulk Import on a regular Basis and have created a script to do this. The Problem is that the .csv file has 12 Columns and the table to Import into has 14. To Workaround this discrepancy I have decided to use a Format file. The Problem is that I have no idea how to create one. Can anyone offer any help help or know of any good resources?

    Thanks and regards,

    Kev

  • March 5, 2015 at 9:32 am

    #1781967

    This seems to be what you're after: https://msdn.microsoft.com/en-us/library/ms179250.aspx?f=255&MSPPError=-2147217396

    If you want an alternative method, the simplest thing I can think of is to create a view with 12 of the 14 columns you have in your table and then bulk insert into the view.

    -- Gianluca Sartori

  • March 6, 2015 at 12:10 am

    #1782091

    Hi Gianluca,

    brilliant! Thanks for the link. I'll post again with my solution.

    As for your suggestion......

    It would be a good one in most cases but unfortunately for us it breaks our business logic as the 2 missing fields are automatically inserted during the loading process. It could work for us if I bulk insert into a temporary table and then move the data from there into where it is supposed to be but that adds an unnecessary complexity that isn't desireable......

    Many thanks for your help!

    Regards,

    Kev

  • March 6, 2015 at 9:02 pm

    #1782333

    spaghettidba (3/5/2015)

    This seems to be what you're after: https://msdn.microsoft.com/en-us/library/ms179250.aspx?f=255&MSPPError=-2147217396

    If you want an alternative method, the simplest thing I can think of is to create a view with 12 of the 14 columns you have in your table and then bulk insert into the view.

    +1000. The view is a great alternative to a format file for such a thing.

    For how to do the format file for such a thing, some guy wrote an article that looks kinda easy to understand. Here's the link.

    http://www.sqlservercentral.com/articles/BCP+(Bulk+Copy+Program)/105867/

    --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)

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

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