Técnicas de copia masiva y exportación e importación masivas en SQL Server

A veces, es necesario exportar o importar grandes cantidades de datos a o afuera de una base de datos. Esas operaciones son llamadas operaciones de exportación e importación masivas.

SQL Server provee herramientas para realizar operaciones de importación y exportación masivas y copia masiva:

La utilidad bcp, la función OPENROWSET(BULK), el Asistente SQL Server Import and Export, la sentencia BULK INSERT y la sentencia SELECT INTO.

Este artículo explica cómo copiar grandes cantidades de datos de una tabla o conjunto de tablas a otras bases de datos usando estas herramientas y usando ApexSQL Script y ApexSQL Data Diff.

Copiando datos usado el Asistente SQL Server Import and Export

El Asistente Import and Export crea un paquete de Integration Services (SSIS) que puede ser programado para correr cada cierto tiempo y que puede ser modificado usando SQL Server Data Tools.

Para iniciar el Asistente Import and Export, haga clic derecho en la base de datos y seleccione el comando Tasks -> Export:

En la ventana Choose a Data source, conéctese a la base de datos fuente. Los siguientes permisos son requeridos para usar el Asistente SQL Server Import and Export en un destino fuente: lea los datos desde la base de datos o archivo, INSERTE el permiso en la base de datos msdb para grabar el paquete SSIS.
En la ventana Choose a Destination, conéctese a la base de datos destino. Los permisos requeridos para una instancia destino son: escribir datos a la base de datos o archivo, permisos para crear una base de datos o una tabla si es necesario:
En la ventana Specify Table Copy or Query, elija la opción Copy data from one or more tables or views.
Elija las tablas y vistas y elija la opción Run immediately, en la ventana completa del Asistente:

Note que el Asistente SQL Server Import and Export no tratará una columna de identidad de manera diferente que a cualquier otra columna y fallará cuando se intente insertar datos en una tabla que tiene una columna identidad:

Si una tabla fuente tiene una restricción de llave externa note que el Asistente SQL Server Import and Export no carga tablas en un orden específico por restricciones, y es posible que una tabla que contiene una llave externa sea cargada antes que una tabla que contiene la llave primaria referenciada, lo cual causará una falla de restricción de llave externa.

Técnicas de copia masiva en SQL Server

Utilidad bcp

La utilidad bcp es una herramienta de línea de comando que permite la exportación masiva de datos desde una base de datos fuente a un archivo o la importación masiva de datos desde un archivo a una base de datos destino. Por ejemplo, para exportar todos los EmployeeIDs y Emails al archivo Email.txt desde la tabla EmployeeEmail en la base de datos TestDB, corra el siguiente comando bcp:

bcp TestDB.dbo.EmployeeEmail out Emails.txt –c -T

La opción –c especifica que la utilidad está siendo usada con datos de caracteres y que la opción –T especifica una conexión confiable, lo que significa que la utilidad bcp usa las credenciales de ingreso del usuario que está actualmente conectado. Si la opción –T no es especificada, un nombre de usuario y una contraseña deben ser especificados con las opciones –U y –P.

La tabla debe existir previamente a la importación. Antes que los datos sean importado a la tabla destino, la tabla en una instancia destino tiene que tener columnas que coinciden con el tipo de datos a ser importados. Para insertar datos desde el archivo Emails.txt a la tabla NewSchema.dbo.EmployeeEmail use el siguiente comando bcp:

bcp NewSchema.dbo.EmployeeEmail in Emails.txt -T –c

La utilidad bcp solo puede exportar datos a un archivo o importar datos desde un archivo. La entidad de seguridad debe tener permisos Select en la tabla fuente para usar la utilidad.

Importar datos usando la sentencia BULK INSERT

Para importar datos desde un archivo específico o tabla, la sentencia BULK INSERT puede ser usada. Al igual que la utilidad bcp, la tabla destino tiene que existir y tener los tipos de columnas que coinciden con los tipos de datos que serán importados.

Especifique la tabla destino y la localización del archivo del cual se importan los datos:

BULK INSERT TestRemote.dbo.EmployeeEmail 
FROM 'C:\Users\Lenovo\Desktop\Emails.txt';
GO

Importar datos usando la función OPENROWSET(BULK)

La función OPENROWSET(BULK) se conecta a una fuente de datos OLE DB para restaurar datos y permite el acceso a datos remotos conectándose a una fuente de datos remota.

INSERT … SELECT * FROM OPENROWSET(BULK…)

INSERT INTO AllEmails(Email)
SELECT * FROM OPENROWSET(
   BULK 'C:\Users\Lenovo\Desktop\Emails.txt',
   SINGLE_BLOB) AS x;

La función OPENROWSET(BULK) provee una alternativa para acceder a los objetos desde un servidor enlazado y es adecuado para acceder a un registro desde una fuente remota.

Importar y Exportar datos usando SELECT INTO

Cuando la cláusula INTO es usada en combinación con la sentencia SELECT permite crear una nueva tabla basada en el resultado de una sentencia select. Por ejemplo, para copiar la tabla EmployeeEmail en la misma instancia, en el esquema por defecto de la base de datos TestDB, ejecute la siguiente consulta:

SELECT * INTO TestDB.dbo.EmployeeEmail 
FROM AdventureWorks2012.HumanResources.EmployeeEmail;

SELECT INTO no puede ser usado para crear una nueva tabla en una instancia SQL Server remota, pero la fuente remota no puede ser incluida en la sentencia SELECT, si hay un enlace a la instancia remota.

Si la tabla fuente contiene restricciones, índices y desencadenadores ellos no serán transferidos a la nueva tabla. La columna en la nueva tabla creada no heredará la propiedad IDENTITY de la salida de la consulta si la sentencia SELECT contiene una función agregada, la cláusula JOIN o la cláusula GROUP BY, y si la columna identidad es usada en una expresión, usada más de una vez o es usada desde una fuente de datos externa.

Exportar e Importar datos usando ApexSQL Script

Usando ApexSQL Script, una herramienta de migración de bases de datos SQL Server, cree un SQL Script para exportar datos, o datos y estructura, desde una instancia fuente y ejecútelo en una instancia destino para importar datos.

Para codificar los datos de la base de datos usando ApexSQL Script inicie un nuevo diálogo de proyecto, conéctese a una base de datos y dependiendo de la estructura en la instancia destino configure las opciones del proyecto. Si ambos, los datos y la tabla fuente son codificados, bajo la pestaña Structure script asumiendo que una base de datos destino existe, deseleccione la opción Script CREATE DATABASE y la opción Script USE for a database. Si una tabla que contiene restricciones, índices o llaves externas es codificada, seleccione la opción Script names ara codificar los nombres de esos objetos:/p>

Bajo la pestaña Data script, seleccione la opción Set IDENTITY_INSERT ON, para manejar la carga de datos en las tablas que tiene columnas IDENTITY. Bajo las opciones Rows to script seleccione la opción Script rows as INSERT:

Si una base de datos destino tiene una diferente estructura de la base de datos fuente, en la pestaña Schema mapping seleccione la opción Exclude schemas para excluir los esquemas desde los nombres de los objetos y haga clic en el botón Open:

En la Cuadrícula de resultados, debajo de la Cuadrícula de datos, filtre las columnas de las que se extraerán datos:

Desde la Cuadrícula de estructura, seleccione las tablas a codificar e inicie el Asistente de Script:

En el Asistente de Script, seleccione el modo de codificación y haga clic en el botón Next:

Grabe el script y ejecútelo en la misma instancia o una remota:

Dialog showing the execution of saved SQL script

Copiar datos usando ApexSQL Data Diff

Si una tabla destino ya existe y tiene la misma estructura de la tabla fuente, usted puede usar ApexSQL Data Diff, una herramienta de comparación de datos SQL que puede copiar datos desde una tabla o tablas a una base de datos destino.

Inicie un nuevo proyecto, conéctese a las bases de datos fuente y destino, y haga clic en el botón Compare: