What is the fastest way to insert a large number of records into a SQL Server database?

Question

I need to insert millions of records read from disk to SQL server. I parse them from a file on one machine, in a single-threaded process.

Ideally, I would like it to work well if the SQL server is local or remote. This needs to be done programmatically in C #.

Answer 1

The fastest way is to use SSIS with concurrent reads, NUMA affinitizes clients, write partitions, and switch all partitions to a single table at the end. This will load you more than 2 TB per hour .

, , , .

, SqlBulkCopy.WriteToServer, IDataReader, SaglCommand . , , trhoughput. .

Answer 2

. SqlBulkCopy, SQL Server.

Answer 3

bcp . , , , #.

Answer 4

Depending on the format of the input file (1) it is suitable for bulk copy, so use it, (2) it is not suitable for bulk copy or you need additional processing / verification / etc. server side so DO jump with multi-threaded inserts, commit every 1000 rows or more and maybe use array inserts