Good day. There is a need to insert a very large number of rows in the database. In this regard, two questions.

  1. How many rows should I insert in one query?

  2. Is there a better way to insert large data arrays into a database than to do a large number of INSERT queries?

  • 3
    what kind of database are we talking about? And what is the input format - CSV, another table, something else? - MaxU
  • @MaxU, DB - MSSQL, I can also bring the data file to CSV, if this format is something better. - Xander

1 answer 1

In this case, I would use the BCP (Bulk Copy Program) , which was specifically designed to load large amounts of data into MS SQL Server

Example:

bcp WorlWideImporters.Warehouse.StockItemTransactions OUT D:\BCP\StockItemTransactions_native.bcp -m 1 -b 10000 -n -e D:\BCP\Error_out.log -o D:\BCP\Output_out.log -S -T

If you need to do it programmatically, use BULK INSERT (Transact-SQL)

Example:

 BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail FROM 'f:\orders\lineitem.tbl' WITH ( FIELDTERMINATOR =' |', ROWTERMINATOR =' |\n' );

The links you can find more examples of use ...

  • Attach one of the examples "to the studio" to your answer; suddenly the link will become irrelevant. - Makarenko_I_V
  • @Makarenko_I_V, I did not think about it, thanks! Now add ... - MaxU