Prashanth Jayaram

El comando BCP (Programa de copia masiva) en acción

October 14, 2019 by

Existen muchos métodos disponibles para operaciones de datos masivos.

  1. Utilidad BCP
  2. Insertar a con Bulk Insert
  3. Usando OPENROWSET
  4. Asistente de importación / exportación

La utilidad del BCP BCP (Bulk Copy Program) es una línea de comando con el cual se programa la copia masiva de datos entre una instancia de SQL y un archivo de datos utilizando un archivo de formato especial. La utilidad BCP se puede utilizar para importar grandes cantidades de filas en SQL Server o exportar datos de SQL Server a archivos. Los archivos de datos BCP no incluyen los detalles de esquema ni información de formato. Entonces, se recomienda poder crear un archivo de formato para tener que registrar el formato de datos, por lo tanto, en caso de fallas, se puede consultar el archivo de formato y así poder comprender mejor el formato de datos para así determinar qué puede haber salido mal.

Hemos estado usando la herramienta BCP durante un largo tiempo, debido a que tiene una sobrecarga muy baja y funciona muy bien para la exportación e importación masiva de datos. Esta es una de las formas más eficaces de manejar la importación y exportación masiva de datos.

Resumen del artículo

En este artículo vamos a explicar la utilidad BCP en detalle. El cual va a cubrir los siguientes temas:

  1. Comando de importación y exportación de BCP
  2. El archivo de formato
  3. Cómo crear un archivo de formato
  4. Cómo usar el archivo de formato para la gestión de datos
  5. Y más…

Configuración

El SQL Server admite la exportación de datos de una tabla de SQL Server e importarlos a otra tabla. Para poder ejecutar el comando BCP en ssms, tiene que habilitar el parámetro de configuración del servidor xp_cmdshell. Esto le va a dar el control para ejecutar procedimientos almacenados extendidos. Pero tiene que tener en cuenta que esta no siempre es una opción recomendada, ya que expone la superficie de SQL Server a posibles amenazas del mundo exterior.


BCP IN | OUT | Opciones de Consulta

OUT: esta opción se utiliza para poder exportar (o volcar) todos los registros de una tabla a un archivo de datos. Como por ejemplo,

  1. SQLShackDemoATC.dbo.SQLShackDemo: este es el nombre de la tabla que vamos a pretender exportar. También podemos usar la opción -d para poder incluir el nombre de la base de datos.
  2. c :\SODetail_Out.txt – este es el archivo de salida en el cual se descargan los datos
  3. -T – Autenticación confiable de Windows
  4. -t, – define la coma como el separador de campo
  5. -w – utiliza el formato de datos de ancho
  6. -b1000 – exporta los datos en lotes de 1000 filas

IN: Esta opción se utiliza para importar todos los registros a una tabla ya existente. Esto va a requerir que se cree la tabla antes de ejecutar el comando BCP.

Vamos a crear una tabla para importar los datos, llamada SalesOrderDetailsIn utilizando la siguiente declaración para su creación:

Podemos ver que en el siguiente comando BCP que se usa la palabra clave IN para importar los datos del archivo SODetails_Output.txt.

BCP QUERYOUT

En el ejemplo a continuación, no vamos a exportar una tabla completa. Vamos a ver una forma de ejecutar una instrucción SQL para tratar de generar un copiado de datos. El comando de exportación BCP se utiliza junto con la instrucción SELECT y la opción de consulta. Al utilizar queryout, la instrucción SQL se puede ejecutar en una conexión definida y los datos se pueden volcar en otro archivo; los otros interruptores son iguales en este ejemplo también.

Formato de Archivos

La utilidad BCP admite el uso de un archivo de formato que contiene los detalles de formato de cada campo en un archivo. El archivo de formato se usa para poder proporcionar todos los detalles necesarios de formato para las operaciones de exportación masiva e importación masiva.

  1. Proporciona una mas forma flexible de interpretar los datos
  2. Proporciona una interfaz para poder volver a formatear los datos durante el proceso de exportación
  3. El archivo de formato elimina la necesidad de programas especiales para operaciones de importación y exportación de datos

SQL Server admite dos tipos de archivos de formato, los cuales son: archivos de formato XML y archivos de formato no XML. El archivo de formato no XML es el formato original admitido por versiones anteriores de SQL Server.

En esta sección de artículo, veremos algunos de los conceptos del archivo de formato, además veremos cómo poder crear el archivo de formato y otros detalles

Comencemos de una vez:

Crear un archivo de formato no XML

Un archivo de formato no XML es un archivo ordinario con una estructura bien definida y el cual contiene metadatos sobre la columna, como el tipo de almacenamiento, la longitud del prefijo, la longitud del campo y el terminador de campo.

El siguiente comando BCP se va a utilizar para poder crear un archivo de formato:

  1. La versión 10.0 es el número de versión de la utilidad BCP
  2. Número de columnas es el número de campos en el archivo de datos; en este caso, son 3
  3. Los otros campos en el archivo de formato describen la naturaleza de los campos de datos
  4. El orden de campo (Field Order) nos indica la posición de cada campo
  5. El tipo de datos describe la clase de datos
  6. Prefijo Len es la longitud de los caracteres del prefijo
  7. Datos Len es la longitud máxima permitida para los datos para el campo específico
  8. Terminator es el delimitador utilizado para poder separar dos campos de los datos
  9. El orden de las columnas (columna order) es la organización de las columnas de la tabla de SQL Server
  10. Nombre de columna (Column name) es la denominación de la columna de la tabla de SQL Server
  11. Collation es la clasificación utilizada para poder almacenar los caracteres en el archivo de datos

Si se usa -f con la opción de formato, el archivo de formato especificado se crea para la tabla o vista especificada. Para poder crear un archivo de formato XML, tiene que especificar la opción -x.

Vamos a ver un ejemplo para omitir la columna ENAME de una tabla y así poder cargar los datos en la tabla SQLShackDemoSkip utilizando un archivo de formato. Antes de cambiar el archivo de formato, vamos a crear la tabla SQLShackDemoSkip y un archivo de datos con datos EID y EJOB utilizando la tabla SQLShackDemo.

Ahora ejecute el comando BCP para generar el archivo de datos:

A continuación, se va a mostrar la salida del archivo de datos:

Ahora, tiene que modificar el archivo de formato, el primer campo de datos se asigna a EID, omite ENAME y la tercera fila asigna el segundo campo de datos del archivo de datos a EJOB. La carga debe ignorar la columna ENAME.

Para poder omitir una columna de tabla, tiene que modificar la definición del archivo de formato de la fila correspondiente y establezca los valores en 0 para todas las columnas relacionadas como se muestra a continuación.

Tiene que ejecutar el siguiente comando BCP para cargar los datos en la tabla SQLShackDemoSkip.


El proceso de carga excluye la segunda columna de SQlShackDemoSkip.

Resumen

La utilidad BCP, que es muy familiar para todos los administradores de SQL Server, funciona muy bien, es rápida y eficiente en términos de importación y exportación de datos. La mayoría de las opciones en el comando BCP se distinguen entre mayúsculas y minúsculas. Se le recomienda que asumamos que todo se distingue entre mayúsculas y minúsculas, para que nunca tengamos ningún problema al usar el comando.

BCP es capaz de poder exportar e importar grandes cantidades de datos, pero si está exportando/importando datos en el rango de decenas de millones, se le recomienda dividirlos en trozos más pequeños. Y esto puede estar usando la instrucción SELECT con la cláusula WHERE. Cuando incorporamos los datos, debemos tener en cuenta el ser cautelosos con las restricciones definidas. Ya que a veces necesitamos eliminar la restricción y volcar los datos y después volver a agregar esas restricciones eliminadas.

Tabla de contenido

Comenzar a crear aplicaciones con las herramientas DevOps de SQL Server
Descripción general de la utilidad SQLCMD en SQL Server
El comando BCP (Programa de copia masiva) en acción
Entrega continua de bases de datos (CD) con SQL Server Tools SqlPackage.exe
Todo sobre MSSQL-Scripter, la herramienta de secuencias de comandos multiplataforma de SQL Server
Introducción a SQL Operations Studio (SOS); instalación inicial y configuración

Prashanth Jayaram

Prashanth Jayaram

Soy un experto en tecnologías con más de 11 años de experiencia en tecnologías de base de datos. Soy Microsoft Certified Professional y tengo el respaldo de una Licenciatura en Master en aplicaciones de computadoras.

Mi especialidad es el diseño y la implementación de soluciones de alta disponibilidad y la migración de bases de datos multiplataforma. Las tecnologías en las que trabajo actualmente son SQL Server, PowerShell, Oracle y MongoDB.

Ver todas las publicaciones de Prashanth Jayaram
Prashanth Jayaram
510 Views