Below is an example of bcp command export a list of employee names and phone numbers to a CSV file. The command could be executed from the command line or as part of a batch file.
bcp "SELECT LastName, FirstName, HomePhone FROM Northwind.dbo.Employees ORDER BY LastName, FirstName" queryout "employees.csv" -T -S . -w -t,
The first part "bcp" is the name of the executable program. When using it at the command line or in a batch file make sure that its parent directory is included in your path variable. Next in quotes is the SELECT statement that will generate the results. I am using the sample Northwind database. I have included the database name "Northwind" and schema "dbo" in my FROM clause. The queryout parameter instructs the bcp utility to export the query results to a file specified by the next parameter "employees.csv". The –T specifies that integrated authentication will be used. If we wanted to use SQL authentication this is also supported. The –S parameter specifies the server, in this case the local machine. The –w parameter instructs bcp to treat the data as Unicode characters. The –t parameter specifies that a comma be used as the field delimited instead of the default tab character. Note that command arguments are case-sensitive "-t" is not the same as "-T".
Here is a screen shot of the employees.csv text file.
You can see from the screenshot that the files created using the bcp utility do not include column headings.
This post is a basic introduction to the bcp utility. The bcp utility has other capabilities: it can be used to import data, similar to the BULK IMPORT statement, and it can also be used to create format files.
This post is a basic introduction to the bcp utility. The bcp utility has other capabilities: it can be used to import data, similar to the BULK IMPORT statement, and it can also be used to create format files.
References
Bcp Utility from SQL Server Books Online:
No comments:
Post a Comment