Export data from a SQL Server table

To import and export data from SQL Server,  Microsoft provides the tool bpc, which operates by command line. Although it would be appreciated if SQL Server Management Studio would provide some graphical utility to import / export without using the command line. Bpc is very easy to use, at least for simple things. It is also one of the most efficient methods to export or import data into SQL Server.

The basic syntax of this command is this:

bcp {table or query} {in / out} file_name [options]

The options can be complicated enough, and you can even build and use format files, but for most of the cases may be sufficient to use the most basic options that allow us to use a file for export / import data from a table:

• Exporting data from table 'mytable' of the database 'MyDatabase' in the file 'dataofmytable.txt'
  
> bcp Mydatabase.myuser.mytable out c:\myfolder\dataofmytable.txt -c -T 
   
• Importing data from file 'dataofmytable.txt' on table 'miothertable' of BD 'miotherdatabase':
  
> bcp myotherdatabase.myuser.myothertable in c:\myfolder\dataofmytable.txt -c -T

It is easy, isn't it? Although, for example, if we want to migrate exported data to another database manager different than SQL Server, we must further refine the use of options to define a file format so that it will be compatible with the import utility of the other DBMS that we will use, or we can also raise another options:

• Create file within the same insert statements, rather than just data. 
  Making export from a SELECT statement with the queryout option, may include INSERTS. Only it should be noted that the syntax is compatible with the database in which data were inserted. 
  I've tried it with a MySQL database, and thus generates a file that can be imported directly to the import interface of phpmyadmin, for example:

  >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

  With id_tipovia I had to make a cast to convert INT data to VARCHAR and that it will be consistent with the string of INSERT. 
  The result is a file containing pruebatipovia.dat with the following data:

  
  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'); ...
  
  To import the file from MySQL only would have to open it in a phpmyadmin browser and select the file generated with bcp to import it, or do the import also from the command line.

      
• Another option would be to use a tool that can connect to different database managers, and has export/import capabilities. Using Oracle SQL Developer , for example, you can work on the same environment with Oracle databases, SQL Server, Access and MySQL. 
  It does export to a file from the database source and then import the file from the target database.