Post reply

Fastest way to load a CSV file in SQL Server Table

  • January 28, 2020 at 7:49 am

    #3720830

    What is the fastest way to load a file in SQL Server Table ?

    I have large CSV Files ( 3-4 GB ) which I load through BULK INSERT. I find this slow compared to BCP.

    I am unable to load it using BCP as the data types are not supported ( INT,VARCHAR, etc)

    Please help.

    Thanks in advance

  • January 28, 2020 at 11:04 am

    #3720857

    BCP would work, you may need to create the needed format file for it to load it.

    What errors do you get as loading INT VARCHAR etc via BCP does work.

    Alternatives are things like SSIS, SAP Data Services etc.

     

    You may even want to play with changing the recovery model (assuming your in FULL), while the bulk load is happening, switch from FULL to BULK LOGGED then back to FULL, but you would need to look into the differences between that and if it falls into the agreed RPO/RTO's etc.  That way it's in a minimal logging rather than a full logging mode for the duration of the BULK INSERT.

  • January 29, 2020 at 5:18 am

    #3721092

    Thanks Anthony.Green

    BCP supports CSV Load when all the destination fields in the DB is of character type. That's where the challenge is.

    I was using FULL and Bulk Logged and later changed it to Simple. I did that as the transaction log started growing crazy.

    It is fine for me as this is not a permanent data store and is used only for the ETL .

    I haven't tried SSIS or SAP DS. Will check it out.

    Thanks again.

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

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