What is the proper way to insert millions of records from a csv file into a SQL Server database?

c# csv database sqlbulkcopy sql-server

Question

I have 40 million entries from a database to insert..csv The procedure I used is outlined in the section below.

  • Service 1 for Windows:

    • looking at the CSV
    • check the records' accuracy
    • utilizing the success table (intermediate table) to add valid entriesSqlBulkCopy .
  • Computer Service 2.

    • obtaining 10,000 records at a time from the success table
    • a search being conducted for these 10,000
    • transferring each record to the database for insertion into the primary table.

Windows Service 1 completes the operation in roughly 30–40 minutes, however Windows Service 2 takes around 5 hours (minimum time). I have two options, but I'm open to ideas since I can't determine which is better.

  1. creating four distinct Windows services and concurrently processing 40,000 records
  2. While loop may be used with a job watch.
  3. calling a Windows service method in async

The fact that we are using transactions in this function and that async will work with them is my greatest concern since, in my theory, utilizing transactions locks the table and requires other processes to operate on it.

1
1
5/28/2016 5:23:31 PM

Popular Answer

I believe you are using the incorrect tool for the task. While c# applications could work, integration services provide a far more effective alternative.

I'm speculating, but these intermediary tables are for transformation or checking, or even for batching the import down.

SSIS's log limit and SQL Bulk Import capabilities may be used to do all of these tasks. Currently, I import hospital data, which involves over 8,000,000 records every night, and it just takes me a few minutes, not hours.

Zzz-17-Zzz is a wonderful resource for understanding how SQL handles such huge data inputs.

2
5/28/2016 11:39:03 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow