Inserte 2 millones de filas en SQL Server rápidamente

Tengo que insertar alrededor de 2 millones de filas de un archivo de texto.

Y con la inserción tengo que crear algunas tablas maestras.

¿Cuál es la mejor y más rápida forma de insertar un conjunto tan grande de datos en SQL Server?

Puedes probar con la clase SqlBulkCopy .

Permite cargar de forma masiva una tabla de SQL Server con datos de otra fuente.

Hay una buena publicación en el blog sobre cómo puedes usarla.

  1. Creo que es mejor que lea los datos del archivo de texto en DataSet

  2. Pruebe SqlBulkCopy – Inserción masiva en SQL desde la aplicación 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(); 

o

después de hacer el paso 1 en la parte superior

  1. Crear XML desde DataSet
  2. Pase XML a la base de datos y haga la inserción masiva

Puede consultar este artículo para obtener más información: Inserción masiva de datos utilizando C # DataTable y SQL Server Función OpenXML

Pero no se ha probado con 2 millones de registros, sino que consumirá memoria en la máquina ya que tiene que cargar 2 millones de registros e insertarlos.

Re la solución para SqlBulkCopy:

Utilicé StreamReader para convertir y procesar el archivo de texto. El resultado fue una lista de mi objeto.

Datatable una clase que toma Datatable o List y un tamaño Buffer ( CommitBatchSize ). Convertirá la lista en una tabla de datos utilizando una extensión (en la segunda clase).

Funciona muy rápido. En mi PC, puedo insertar más de 10 millones de registros complicados en menos de 10 segundos.

Aquí está la clase:

 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 { public IList 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(this IEnumerable 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; } } 

}

Aquí hay un ejemplo cuando quiero insertar una lista de mi objeto personalizado List ( ListDetections ):

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

La clase BulkInsert se puede modificar para agregar la asignación de columnas si es necesario. Ejemplo que tiene una clave de identidad como primera columna. (Suponiendo que los nombres de las columnas en la tabla de datos son los mismos que la base de datos)

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

Me encontré con este escenario recientemente (más de 7 millones de filas) y terminé usando sqlcmd a través de powershell (después de analizar datos brutos en sentencias SQL insert) en segmentos de 5.000 a la vez (SQL no puede manejar 7 millones de líneas en un solo trabajo) o incluso 500,000 líneas, a menos que esté dividido en trozos más pequeños de 5 K. Luego puede ejecutar cada secuencia de comandos de 5K una tras otra) ya que necesitaba aprovechar el nuevo comando de secuencia en SQL Server 2012 Enterprise. No pude encontrar una forma programática para insertar siete millones de filas de datos de manera rápida y eficiente con dicho comando de secuencia.

En segundo lugar, una de las cosas a tener en cuenta al insertar un millón de filas o más de datos en una sola sesión es el consumo de CPU y memoria (principalmente memoria) durante el proceso de inserción. SQL consumirá memoria / CPU con un trabajo de esta magnitud sin liberar dichos procesos. No hace falta decir que si no tienes suficiente poder de procesamiento o memoria en tu servidor, puedes colgarlo con bastante facilidad en poco tiempo (lo que descubrí por el camino difícil). Si llega al punto donde su consumo de memoria supera el 70-75%, simplemente reinicie el servidor y los procesos volverán a la normalidad.

Tuve que ejecutar una serie de pruebas de prueba y error para ver cuáles eran los límites de mi servidor (teniendo en cuenta los recursos limitados de CPU / memoria) antes de que pudiera tener un plan de ejecución final. Sugeriría que hagas lo mismo en un entorno de prueba antes de poner esto en producción.

Yo uso la utilidad bcp. (Progtwig de Copias a Granel) Cargo aproximadamente 1.5 millones de registros de texto cada mes. Cada registro de texto tiene 800 caracteres de ancho. En mi servidor, toma aproximadamente 30 segundos agregar los 1,5 millones de registros de texto en una tabla de SQL Server.

Las instrucciones para bcp están en http://msdn.microsoft.com/en-us/library/ms162802.aspx