5

I am importing huge data file of size around 48 gb using SQL Server default Import Tool. It keeps on executing fine for app. 13000000 Row insertions but after it the Task Fails with following error. I cant open the csv as its damn huge neither can i move row by row in it and analyze the stats. I am really confused how to handle this.

ERROR- Details SQL Server

Improve this question
2
  • Have you tried increasing the size of the column mentioned in the error message?
    – Erik Darling
    Sep 16, 2017 at 15:42
  • 1
    How about breaking the file into many smaller ones?
    – Sir Swears-a-lot
    Sep 17, 2017 at 3:40

1 Answer 1

Reset to default
13

You can use powershell to fast import large CSV into sql server. This script High-Performance Techniques for Importing CSV to SQL Server using PowerShell - by Chrissy LeMaire (author of dbatools)

Below is the benchmark achieved :

5.35 million rows a minute for non-indexed tables and

4.35 million rows a minute for tables with clustered indexes.

The script even batches your import into 50K rows so that during import, it does not hog the memory.

Edit: ON SQL Server side -

  • Make sure you have proper recovery model set .. for such load, bulk recovery is best setting.
  • Autogrowth is set to proper MB and not Percent.
  • Both data and log files are provisioned big enough so there is minimal autogrowth events.
  • Do the import during non peak hours.
  • Follow best practices described in Data loading perf guide and or my answer.
  • Instant File Initialization is enabled and power setting is set to high perf.
Improve this answer
2
  • 1
    What if I need to do something like this but during peak hours (many time per hours)? My data is smaller like a 15 MB Excel file. Using it as a linked server seems slow (I would guess because Excel data is not indexed).
    – bbigras
    Aug 31, 2018 at 13:15
  • 3
    Load it in a staging table and then merge it.
    – Kin Shah
    Aug 31, 2018 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.