¿Cómo mantener el orden de las filas con SqlBulkCopy?

excel export import sqlbulkcopy sql-server

Pregunta

Estoy exportando datos de manera programática de Excel a SQL Server 2005 utilizando SqlBulkCopy. Funciona muy bien, el único problema que tengo es que no conserva la secuencia de filas que tengo en el archivo de Excel. No tengo una columna para ordenar, solo quiero que los registros se inserten en el mismo orden en que aparecen en la hoja de cálculo de Excel.

No puedo modificar el archivo de Excel y tengo que trabajar con lo que tengo. La clasificación por cualquiera de las columnas existentes romperá la secuencia.

Por favor ayuda.

PS terminó insertando la columna de ID en la hoja de cálculo, parece que no hay manera de mantener el orden durante la exportación / importación

Respuesta aceptada

No creo que el orden de las filas esté especificado o garantizado por SQL a menos que use una cláusula "ORDER BY".

De una publicación de Bill Vaughn ( http://betav.com/blog/billva/2008/08/sql_server_indexing_tips_and_t.html ):

Uso de orden por: incluso cuando una tabla tiene un índice agrupado (que almacena los datos en orden físico), SQL Server no garantiza que las filas se devuelvan en ese orden (o cualquier orden particular) a menos que se use una cláusula ORDER BY.

Otro enlace con información:

http://sqlblogcasts.com/blogs/simons/archive/2007/08/21/What-is-thetheposition-of-a-row--.aspx


Respuesta popular

Después de mucha investigación, parece evidente que no hay manera de retener el orden de las filas con el comando Bulk Insert escrito como lo presenta Microsoft. O bien debe agregar una columna de ID directamente en el archivo de importación, usar un shell u otro script externo, o prescindir de él. Parece que sería una característica necesaria (y fácil) para que Microsoft la agregue, pero después de más de una década sin nada de eso, no va a suceder.

Sin embargo, necesitaba preservar el orden de registro real en el archivo de importación después de importar, ya que los registros superiores reemplazarán a aquellos inferiores si una columna establecida tuviera el mismo valor.

Así que fui por una ruta diferente. Mis limitaciones fueron:

  • No pude cambiar el archivo fuente en absoluto. (y sienta un mal precedente!)
  • No pude usar un script externo. Demasiado complicado. Tenía que ser una solución simple basada en T-Sql, no ejecuciones CMD. Esto necesitaba ir en un solo procedimiento para poder ser automatizado.

Me gustó la lógica de usar Powershell para crear instrucciones de inserción ordenadas para cada fila y luego ejecutarse en Sql. Básicamente, se puso en cola cada registro para una inserción individual en lugar de una inserción A GRANEL. Sí, funcionaría, pero también sería muy lento. A menudo tengo archivos con 500K + filas en ellos. Necesitaba algo RÁPIDO.

Así que me encontré con XML. Carga masiva el archivo directamente en una sola variable XML. Esto mantendría el orden de los registros, ya que cada uno se agrega al XML. Luego analice la variable XML e inserte los resultados en una tabla, agregando una columna de identidad al mismo tiempo.

Se supone que el archivo de importación es un archivo de texto estándar, con cada registro que termina en un salto de línea (Char (13) + Char (10))

Mi enfoque tiene 2 pasos:

  1. Ejecute la instrucción SQL IMPORT (usando OPENROWSET), encapsulando cada registro con etiquetas XML. Captura los resultados en una variable XML.

  2. Analice la variable mediante las etiquetas XML en una tabla, agregando una columna incremental [ID].

    ---------------------------------
    Declare @X xml;
    ---------------------------------
    SELECT @X=Cast('<X>'+Replace([BulkColumn],Char(13)+Char(10),'</X><X>')+'</X>' as XML)
    FROM OPENROWSET (BULK N'\\FileServer\ImportFolder\ImportFile_20170120.csv',SINGLE_CLOB) T
    ---------------------------------
    SELECT [Record].[X].query('.').value('.','varchar(max)') [Record]
    ,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) [ID]
    --Into #TEMP 
    FROM @X.nodes('X') [Record](X);
    ---------------------------------
    
    • Las etiquetas XML reemplazan cada avance de línea.

    • Si el archivo termina con un avance de línea, esto causará que se agregue una fila en blanco al final. Simplemente borre la última fila.

Escribí esto en mi procedimiento usando sql dinámico para poder pasar el Nombre de archivo y configurar la ID para que comience en 1 o 0 (en caso de que haya una fila de encabezado).

Pude ejecutar esto contra un archivo de 300K registros en aproximadamente 5 segundos.



Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué