Existen muchos métodos disponibles para operaciones de datos masivos.
- Utilidad BCP
- Insertar a con Bulk Insert
- Usando OPENROWSET
- 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:
- Comando de importación y exportación de BCP
- El archivo de formato
- Cómo crear un archivo de formato
- Cómo usar el archivo de formato para la gestión de datos
- 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.
|
1 |
EXEC master..xp_cmdshell 'BCP ProdSQLShackDemo.dbo.SQLShackAuthor OUT f:\PowerSQL\ProdSQLShackDemo.txt -T -c' |
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 |
C:\WINDOWS\System32>BCP [AdventureWorks2014].[dbo].[SalesOrderDetail] out C:\SODetail_Out.txt -S hqdbt01\SQL2017 -T -c –b1000 –t, |
- 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.
- c :\SODetail_Out.txt – este es el archivo de salida en el cual se descargan los datos
- -T – Autenticación confiable de Windows
- -t, – define la coma como el separador de campo
- -w – utiliza el formato de datos de ancho
- -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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE [AdventureWorks2014] GO CREATE TABLE [dbo].[SalesOrderDetailIn]( [SalesOrderID] [int] NOT NULL, [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [OrderQty] [smallint] NOT NULL, [ProductID] [int] NOT NULL, [SpecialOfferID] [int] NOT NULL, [UnitPrice] [money] NOT NULL, [UnitPriceDiscount] [money] NOT NULL, [LineTotal] [numeric](38, 6) NOT NULL, [rowguid] [uniqueidentifier] NOT NULL, [ModifiedDate] [datetime] NOT NULL ) ON [PRIMARY] GO |
Podemos ver que en el siguiente comando BCP que se usa la palabra clave IN para importar los datos del archivo SODetails_Output.txt.
|
1 |
C:\WINDOWS\System32>BCP [adventureworks2014].dbo.[SalesOrderDetailIn] in C:\SODetail_Output.txt -S hqdbt01\SQL2017 -T –c |
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.
|
1 2 3 4 5 6 7 8 9 |
BCP "SELECT [SalesOrderID], [SalesOrderDetailID],[CarrierTrackingNumber] FROM [AdventureWorks2014].[dbo].[SalesOrderDetail]" queryout C:\SOQueryOut.txt -S hqdbt01\SQL2017 -T –c CREATE TABLE [dbo].[SalesOrderDetailQueryOut]( [SalesOrderID] [int] NOT NULL, [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL ) C:\WINDOWS\System32>BCP [adventureworks2014].dbo.[SalesOrderDetailQueryOut] in C:\SOQueryOut.txt -S hqdbt01\SQL2017 -T -c |
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.
- Proporciona una mas forma flexible de interpretar los datos
- Proporciona una interfaz para poder volver a formatear los datos durante el proceso de exportación
- 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:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE SQLShackDemo ( EID smallint, EName nvarchar(50) NULL, EJOB nvarchar(50) NOT NULL ); GO INSERT INTO SQLShackDemo values(1,'Prashanth','DB Manager'),(2,'Bob','IT Director'),(3,'Scott','DevOp Eng'); |
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 |
BCP TestDatabase.dbo. SQLShackDemo format nul -c -f c:\BCPImport.fmt -t, -T |
- La versión 10.0 es el número de versión de la utilidad BCP
- Número de columnas es el número de campos en el archivo de datos; en este caso, son 3
- Los otros campos en el archivo de formato describen la naturaleza de los campos de datos
- El orden de campo (Field Order) nos indica la posición de cada campo
-
El tipo de datos describe la clase de datos
- Prefijo Len es la longitud de los caracteres del prefijo
- Datos Len es la longitud máxima permitida para los datos para el campo específico
- Terminator es el delimitador utilizado para poder separar dos campos de los datos
- El orden de las columnas (columna order) es la organización de las columnas de la tabla de SQL Server
- Nombre de columna (Column name) es la denominación de la columna de la tabla de SQL Server
- 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.
|
1 2 3 4 5 6 |
CREATE TABLE SQLShackDemoSkip ( EID smallint, EName nvarchar(50) NULL, EJOB nvarchar(50) NOT NULL ); |
Ahora ejecute el comando BCP para generar el archivo de datos:
|
1 |
C:\WINDOWS\System32>BCP "select EID,EJOB from SQLShackDEMOATC.dbo.SQLShackDemo" queryout c:\SQLShackDemo3.txt -T -S HQDBT01\SQL2017 -c -t, |
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.
|
1 |
C:\WINDOWS\System32>BCP SQLShackDemoATC.dbo.SQLShackDemoSkip in C:\SQLShackDemo3.txt -f C:\BCPformat.fmt -S hqdbt01\SQL2017 –T |
El proceso de carga excluye la segunda columna de SQlShackDemoSkip.
|
1 |
SELECT * FROM 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
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
- Descripción general de la función SQL CAST y SQL CONVERT - December 6, 2019
- Revisión del operador relacional y descripción general dePivot y Unpivot estático y dinámico de SQL - November 6, 2019
- Revisión, ejemplos y uso de SQL Union - November 4, 2019