2

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

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

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.
  • 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). – brunoqc Aug 31 '18 at 13:15
  • 2
    Load it in a staging table and then merge it. – Kin Shah Aug 31 '18 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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