En este post, veremos:
¿Cómo hacer una insercion masiva en SQL Server usando el tipo de datos XML?
¿Cómo automatizamos el proceso?
Crea un SP automáticamente para manejar la inserción de datos.
Transformar una DataTable a XML.
Antecedentes
A veces, es necesario insertar registros de forma masiva en la base de datos, este proceso es muy simple cuando tenemos acceso directo a la base de datos, pero ¿qué sucede cuando tenemos que extraer los datos de un Servicio Web, un Servicio ASP o una RESTful API?
Aquí es cuando comienzo a ver todo tipo de cosas creativas, como insertar uno por
uno cada registro que se extrajo de la llamada, crear un script sobre la marcha, etc.
El Argumento
Tenemos un servicio web que devuelve un DataSet (.NET)
El esquema de la tabla de datos podría cambiar en cualquier momento sin previo aviso.
Tenemos que extraer los datos de varios servidores web.
¿Cómo hacer una insercion masiva en SQL Server usando el tipo de datos XML?
Insertemos algunos datos basados en el siguiente XML:
XQuery en SQL Server es un lenguaje estático. Por lo tanto, para reducir el tipo estático y garantizar que exista, como máximo, un valor, se debe usar el predicado posicional [1] para indicar explícitamente que la expresión devuelve un solo valor.
TIP: Las mayúscula y minúsculas en XML son muy importantes (distingue entre mayúsculas y minúsculas). Las etiquetas de apertura y cierre deben coincidir exactamente. Por ejemplo, <ROOT>, <Root> y <root> son tres etiquetas diferentes.
Leamos el XML e insertemos algunos datos.
Voila, tenemos datos listos para ser consultados, fue muy simple ¿no? Bueno, esto es verdad si tenemos que crear el script solo una vez y dicho script no tiene más de 10 columnas, pero ¿qué sucede cuando tiene varios archivos XML con diferentes esquemas, muchos datos y cada uno va a una tabla diferente? Sí, que será una molestia crear el script, verificar cada tipo de datos y crear todas las tablas.
No te preocupes, te tengo una solución!
El problema
Tenemos que extraer algunos datos de varios servidores, pero no tenemos acceso a la base de datos.
El equipo de IT encontró una solución muy inteligente, por lo que en lugar de permitir el acceso a la base de datos, crearon un servicio web que regresa un DataSet con una sola tabla. ¡¡Esos de IT son geniales!! :P
El equipo de IT proporcionó una imagen del conjunto de datos, esto es lo que debemos obtener cuando llamemos el servicio web, consulte a continuación.
La solución
Lo sé… le dije que tenía una solución, pero aun asi tienes que trabajar un poco para que esto funcione. Lo único que tienes que hacer es crear la tabla de destino basada en la imagen, sí la que vas a usar para almacenar los datos, luego .NET se encargará del resto.
Vamos a crear la tabla de destino.
¿Ahora que?
Tenemos que crear la consulta para insertar los datos, pero esta vez vamos a dejar que .NET haga el trabajo por nosotros, así que he creado un pequeño programa donde puede pasar el nombre de la tabla y el regresara un procedimiento almacenado que corresponde con el esquema de la tabla. Consulte a continuación.
Stored Procedure:
Tenga en cuenta que el xPath es estático “NewDataSet/XMLData”.
.NET Código Fuente.
Esto ejecuta una consulta en mi base de datos:
Esto hace el trabajo:
¿Qué pasa con los datos?
Bueno, los datos no son XML, por lo que tenemos que convertir el DataTable a XML, tengo una función que se encarga de esto. Por favor ver más abajo.
Aquí hay una muestra del XML después de la conversión:
Tenemos los datos, ¿ahora qué?
Tenemos que insertar los datos en SQL ... Sí, tengo otra función que se encarga de esto, tiene que pasar el DataTable y el nombre del SP. Por favor ver más abajo.
Sí, hemos terminado, ahora podemos obtener datos del servicio web y hacer una inserción masiva enSQL.
Esta solución funciona muy bien para archivos de tamaño pequeño / mediano, por lo que cualquier cosa de menos de 2GB se insertará muy rápido. Todo lo que supere los 2GB no funcionará con seguridad, en estas circunstancias, SQLXML Bulk Loader es la mejor solución. Trataré de cubrir esto en mis próximas publicaciones.
Extra
Vamos a hacer algunas pruebas con el programa final, vamos a insertar 300K filas
de forma aleatoria en la base de datos.
La inserción de datos tomó un promedio de 6 segundos, por lo que no es tan malo.
Revisemos los datos.
Conclusión
Bienvenido al mundo XML, esta es una opción valiosa cuando desea insertar datos de forma masiva desde un servicio web, un servicio ASP o una API RESTful.
¡Diviértanse aprendiendo!
Comments