Быстро вставьте 2 миллиона строк в SQL Server

1. Я думаю, что лучше Вы читаете данные текстового файла в DataSet

2. попробовать SqlBulkCopy -массовая вставка в SQL из приложения C#

   // connect to SQL
   using (SqlConnection connection = 
           new SqlConnection(connString))
   {
       // make sure to enable triggers
       // more on triggers in next post
       SqlBulkCopy bulkCopy = 
           new SqlBulkCopy
           (
           connection, 
           SqlBulkCopyOptions.TableLock | 
           SqlBulkCopyOptions.FireTriggers | 
           SqlBulkCopyOptions.UseInternalTransaction,
           null
           );
   
       // set the destination table name
       bulkCopy.DestinationTableName = this.tableName;
       connection.Open();
   
       // write the data in the "dataTable"
       bulkCopy.WriteToServer(dataTable);
       connection.Close();
   }
   // reset
   this.dataTable.Clear();
   

или

после выполнения шага 1 в верхней

1. создать XML из набора данных
2. передать XML в базу данных и сделать массовую вставку

вы можете проверить эту статью для детали : Навальный Вставка данных с помощью функции C# DataTable и SQL server OpenXML

но его не тестировали с 2 миллионами записей, он будет делать, но потреблять память на машине, как вы должны загрузить 2 миллиона записей и вставить его.

Re решение для SqlBulkCopy:

Я использовал StreamReader для преобразования и обработки текстового файла. В результате получился список моих объектов.

Я создал класс, чем требуется Datatable или List<T> и размер буфера (CommitBatchSize). Он преобразует список в таблицу данных с помощью расширения (во втором классе).

он работает очень быстро. На моем ПК я могу вставить более 10 миллионов сложных записей менее чем за 10 считанные секунды.

вот класс:

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{

public class BulkUploadToSql<T>
{
    public IList<T> InternalStore { get; set; }
    public string TableName { get; set; }
    public int CommitBatchSize { get; set; }=1000;
    public string ConnectionString { get; set; }

    public void Commit()
    {
        if (InternalStore.Count>0)
        {
            DataTable dt;
            int numberOfPages = (InternalStore.Count / CommitBatchSize)  + (InternalStore.Count % CommitBatchSize == 0 ? 0 : 1);
            for (int pageIndex = 0; pageIndex < numberOfPages; pageIndex++)
                {
                    dt= InternalStore.Skip(pageIndex * CommitBatchSize).Take(CommitBatchSize).ToDataTable();
                BulkInsert(dt);
                }
        } 
    }

    public void BulkInsert(DataTable dt)
    {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            // make sure to enable triggers
            // more on triggers in next post
            SqlBulkCopy bulkCopy =
                new SqlBulkCopy
                (
                connection,
                SqlBulkCopyOptions.TableLock |
                SqlBulkCopyOptions.FireTriggers |
                SqlBulkCopyOptions.UseInternalTransaction,
                null
                );

            // set the destination table name
            bulkCopy.DestinationTableName = TableName;
            connection.Open();

            // write the data in the "dataTable"
            bulkCopy.WriteToServer(dt);
            connection.Close();
        }
        // reset
        //this.dataTable.Clear();
    }

}

public static class BulkUploadToSqlHelper
{
    public static DataTable ToDataTable<T>(this IEnumerable<T> data)
    {
        PropertyDescriptorCollection properties =
            TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new DataTable();
        foreach (PropertyDescriptor prop in properties)
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        foreach (T item in data)
        {
            DataRow row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            table.Rows.Add(row);
        }
        return table;
    }
}

}

вот пример, когда я хочу вставить список моего настраиваемого объекта List<PuckDetection> (ListDetections):

var objBulk = new BulkUploadToSql<PuckDetection>()
{
        InternalStore = ListDetections,
        TableName= "PuckDetections",
        CommitBatchSize=1000,
        ConnectionString="ENTER YOU CONNECTION STRING"
};
objBulk.Commit();

The BulkInsert класс может быть изменен, чтобы добавить сопоставление столбцов, если это необходимо. Пример у вас есть ключ идентификации в качестве первого столбца.(это при условии, что имена столбцов в datatable совпадают с базой данных)

//ADD COLUMN MAPPING
foreach (DataColumn col in dt.Columns)
{
        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}

недавно я столкнулся с этим сценарием (более 7 миллионов строк) и использовал sqlcmd через powershell (после разбора необработанных данных в инструкции SQL insert) в сегментах по 5 000 за раз (SQL не может обрабатывать 7 миллионов строк в одном кусковом задании или даже 500 000 строк, если только его не разбить на более мелкие части 5K. Затем вы можете запускать каждый сценарий 5K один за другим.) поскольку мне нужно было использовать новую команду последовательности в SQL Server 2012 Enterprise. Я не мог найти программиста способ быстро и эффективно вставить семь миллионов строк данных с помощью указанной команды последовательности.

во-вторых, одна из вещей, на которую следует обратить внимание при вставке миллиона строк или более данных за один сеанс, - это потребление процессора и памяти (в основном памяти) во время процесса вставки. SQL будет съедать память / процессор с заданием такой величины, не выпуская указанные процессы. Излишне говорить, что если у вас недостаточно вычислительной мощности или памяти на вашем сервере, вы можете разбить его довольно легко за короткое время (что я узнал на собственном горьком опыте). Если Вы дойдете до точки, где потребление памяти составляет более 70-75% просто перезагрузите сервер и процессы будут выпущены обратно в нормальное состояние.

Мне пришлось запустить кучу проб и ошибок, чтобы увидеть, какие ограничения для моего сервера были (учитывая ограниченные ресурсы процессора/памяти для работы), прежде чем я мог фактически иметь окончательный план выполнения. Я бы предложил вам сделать то же самое в тестовой среде, прежде чем выкатывать это в производство.

Я использую утилиту bcp. (Программа Массового Копирования) Я загружаю около 1,5 миллиона текстовых записей каждый месяц. Каждая текстовая запись имеет ширину 800 символов. На моем сервере требуется около 30 секунд, чтобы добавить 1,5 миллиона текстовых записей в таблицу SQL Server.

инструкции для bcp находятся в http://msdn.microsoft.com/en-us/library/ms162802.aspx

SqlBulkCopy-это хорошо.

но вы должны подготовить данные не preblem, такие как PK\FK не конфликт, поэтому вставить 200k recored в SQL Server очень легко.