This is a pretty handy little tool in your arsenal. I’ve talked about using bcp to transfer data from one instance to another before and this is another really great use for bcp. If you haven’t used it before bcp stands for Bulk Copy Protocol and is a command line tool for transferring data in and out of SQL Server. In this case you can use this command to generate a csv file from DBName.SchemaName.TableName:
bcp DBName.SchemaName.TableName out PathFileName.csv -S ServerName -T -c -t,
Pretty easy right? Here are a few of the details.
- The flags are case sensative! -T and -t mean two completely different things.
- You can include -d dbname to specify which database you are referencing. In this case I put a three part table name so I didn’t need it.
- You can replace out with queryout and use a query instead of the TableName.
- -T means use trusted authentication. You could use -U and -P to include a SQL Ids user name and password instead. You can’t use them both at the same time.
- -t is the field terminator. I.e. if you used -t| you’ll get a pipe delimited file.
- -c gives you a readable character output. The default is tab delimited which is why I have the -t,. The default row terminator is rn (carriage return, line feed) but can be changed using -r.
There are a ton of other flags and ways to use bcp. I recommend taking a look and at least giving yourself an overview of this highly useful export tool.