SQL Server – Export data using BULK copy command with Example

Sql server provides different options for import and export data from sql server to another media like text file, excel or even in another database. One way of exporting data to excel or flat file is using  import export wizards  option of DTS (Data transformation Service).

Same thing can be done using BULIK copy (bcp command) and SQL Server Integration Package.

BULK Copy (BCP)

Here we are going to discuss about BULK copy command. Using Bulk copy option, you can export data from sql server to either in flat file or excel depending on your requirement.

How you can use bcp to export data from sql server?

Using bulk copy, you can retrieve large data from table using bcp query.

Bulk copy is the fastest way of exporting data from sql server into data file.

Using bcp utility: you can run the bcp on command as .bat file or.cmd scripts to get bulk data from sql server table into data file.

Syntax:

Bcp [query|dbname.dbowner. [tablename |view]]

In|out|queryout|format (Data flow)

Datafile (Output file path)

-T [Trusted Connection]

-t [Field terminator]

-c [Character Type]

-S [Server name]

There are lots of options available in BCP, you can see by typing “bcp /”? in the command prompt.

Let’s go through with an example, here we are using command prompt to run the BCP command. It will export the selected records from employee table into data file separated by comma, after executing it will generate output file in c:\output.txt.

Example of BCP command

SQL Query: select employeeid,Firstname,lastname,Hiredate from northwind.dbo.Employees

BCP command: bcp “select employeeid,Firstname,lastname,Hiredate from northwind.dbo.Employees” queryout c:\output.txt -T -c -t, -SServerName

Here we are using “queryout” option that will allow us to run the sql server query to get the selected records from table using bcp.

exporting data from sql server to data file using bcp

 

 

If you want to export entire table data in a file using bcp command,you can make use of below query.

Command : bcp northwind.dbo.customers out c:\custdb.txt -T –c

Output

C:\Program Files\Microsoft SQL Server\90\Tools\binn>bcp northwind.dbo.customers out c:\custdb.txt -T -c

Starting copy…

91 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total     : 31     Average : (2935.48 rows per sec.)

 

To generate file in excel format use this query : bcp northwind.dbo.customers out c:\custdb.xls -T –c

Leave a Reply

  

  

  


9 − = 8