Exportar datos de una tabla de SQL Server

Para importar y exportar datos de SQL Server Microsoft proporciona la herramienta bpc, que funciona por linea de comandos. Aunque sería de agradecer que SQL Server Management Studio proporcionara alguna utilidad gráfica de importación/exportación que nos evitara tener que recurrir a utilizar la línea de comandos, bpc es muy fácil de utilizar, al menos para hacer cosas sencillas. También es de los métodos más eficientes para exportar o importar datos en SQL Server.

La sintaxis básica de este comando es esta:

bcp {tabla o consulta} {in/out} nombre_del_fichero [opciones]

Las opciones se pueden complicar bastante, e incluso se pueden construir y utilizar ficheros de formato, pero para la mayoría de los casos puede ser suficiente con utilizar las opciones más básicas que nos permitan utilizar un fichero para exportar/importar los datos de una tabla:

  • Exportación de los datos de la tabla 'mitabla' de la BD 'mibasededatos' en el fichero 'datosdemitabla.txt'
> bcp mibasededatos.miusuario.mitabla out c:\midirectorio\datosdemitabla.txt -c -T
  • Importación de los datos del fichero 'datosdemitabla.txt' en la tabla 'miotratabla' de la BD 'miotrabasededatos':
> bcp miotrabasededatos.miusuario.miotratabla in c:\midirectorio\datosdemitabla.txt -c -T

Facilito, no? Pero si, por ejemplo, los datos que exportamos los queremos utilizar para hacer una migración a otro gestor de base de datos diferente de SQL Server, habrá que afinar más en la utilización de opciones para la definición del formato del fichero para que este sea compatible con la utilidad de importación del otro SGBD que vayamos a utilizar, o también nos podemos plantear otras opciones:

  • Crear dentro del fichero las mismas sentencias de inserción, en lugar de sólo los datos.

Haciendo la exportación a partir de una sentencia SELECT, con la opción queryout, se pueden incluir los INSERTS. Sólo hay que tener en cuenta que la sintaxis sea compatible con la de la base de datos donde quedamos insertar los datos.

Yo lo he probado con una base de datos MySQL, y de esta forma se genera un fichero que se puede importar directamente con la interface de de importación de phpmyadmin, por ejemplo:

> bcp "select 'INSERT INTO tipovia (id_tipovia,des_tipovia) VALUES ('+cast(id_tipovia as varchar)+','''+des_tipovia+''');' FROM dataprix.dbo.tipovia" queryout c:\pruebatipovia.dat -T -c

Con id_tipovia he tenido que hacer un cast para convertir los datos de INT a VARCHAR y que sean compatibles con la cadena del INSERT.

El resultado es el fichero pruebatipovia.dat que contendrá los siguientes datos:

INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (2,'ACCE');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (3,'ACEQ');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (5,'ALAM');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (6,'CUSTA');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (6,'ALDAP');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (10,'ANDAD');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (11,'ANGTA');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (12,'APTOS');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (13,'ARB');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (14,'ARRAL');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (15,'ARRY');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (18,'GALE');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (21,'AUTO');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (21,'AVIA');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (22,'BARRI');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (23,'BARDA');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (24,'AVDA');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (26,'BJADA');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (27,'BELNA');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (29,'GTA');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (31,'BLQUE');
...

 

Para importar el fichero desde MySQL sólo habría que abrir en un navegador phpmyadmin y seleccionar el fichero generado con bcp para importarlo, o hacer la importación también desde linea de comandos.

Importación de un fichero de datos con INSERTS desde phpmyadmin (MySQL)
 

  • Otra opción sería utilizar una herramienta que pueda conectarse a diferentes gestores de bases de datos, y que tenga capacidades de exportación/importación. Con Oracle SQL Developer, por ejemplo, se puede trabajar desde el mismo entorno con bases de datos Oracle, SQL Server, Access y MySQL.
    Se hace la exportación a un fichero desde la BD origen, y luego se hace la importación del fichero desde la base de datos destino.

    Exportacion de datos SQL Server desde Oracle SQL Developer

 

En respuesta a por Gonzalo (no verificado)

La ejecución del script de comandos SQL que se han generado en el .dat desde linea de comandos depende de la base de datos para la que quieras ejecutar el script.

Siguiendo con el ejemplo, en MySQL, si no recuerdo mal, tendrías que añadir en la primera linea del .dat el comando USE + el nombre de la base de datos. En nuestro ejemplo, el principio del fichero pruebatipovia.dat quedaría así:

USE miotrabasededatos;
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (2,'ACCE');
INSERT INTO pruebatipovia (id_tipovia,des_tipovia) VALUES (3,'ACEQ');
..

Después, desde línea de comandos sólo tienes que ejecutar MySQL pasándole el fichero. La sintaxis para hacerlo desde el cmd de windows sería tan simple como esta:

C:\>mysql < pruebatipovia.dat

 

Buenas noches quisiera saber desde sql server como puedo hacer que desde un store procedure utilizarlo en un ETL para que exporte dos o mas archivos.

 

A la espera de su ayuda.

 

Buenas noches!

Hola quisiera saber que agregarle a los comandos para que al exportar la data en el archivo plano muestre los encabezados de las columnas.. Gracias

En respuesta a por Anonimo (no verificado)

Con BPC sería complicado sacar datos y cabeceras, yo por lo menos no conozco ninguna opción directa para hacerlo.

Lo que sí puedes hacer es utilizar este método de crear el fichero de exportación a partir de una consulta SQL para crear un fichero con cabeceras y datos en lugar de un fichero con sentencias de INSERT.

Para crear un fichero de salida en formato de salida .csv podrías utilizar algo así como esto:

>bcp "SELECT 'id_tipovia, des_tipovia' UNION SELECT id_tipovia, ',', des_tipovia FROM dataprix.dbo.tipovia queryout c:\datostipovia.csv -T -c

He escrito directamente los nombres de los campos, pero si tienes tablas con muchos campos podrías incluso utilizar el diccionario de datos para que los nombres de los campos se generaran dinámicamente.

No lo he probado pero debería funcionar, si tienes algún problema para hacerlo de esta manera nos lo cuentas..

 

Otra opción es utilizar SQLCMD en lugar de BPC, te dejo un ejemplo de este sitio donde explican muy bien cómo utilizar SQLCMD para generar ficheros .CSV para Excel con SQL Server:

sqlcmd -S . -d AzureDemo50 -E -s, -W -i ExcelTest.sql | findstr /V /C:"-" /B > ExcelTest.csv