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.
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.
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
C:\Program Files\Microsoft SQL Server\90\Tools\binn>bcp northwind.dbo.customers out c:\custdb.txt -T -c
91 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 31 Average : (2935.48 rows per sec.)