Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Using bcp to export data into a file in SQL Server

From Wiki

Jump to: navigation, search

SQL server ships with the Bulk Copy Program (bcp), this tool enables you to generate data files from the command line.

So if I want to dump the result of this query 'SELECT name,number,TYPE FROM master.dbo.spt_values' into this text file 'C:\Ju nkdraw\spt_values_pipe.txt' and use a pipe delimiter I would do this

bcp "select name,number,type from master.dbo.spt_values" queryout C:\Ju nkdraw\spt_values_pipe.txt -T -c -t"|"

From a command line it would look like this

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

P:\SQLDenis>bcp "select name,number,type from master.dbo.spt_values" queryout C:\Ju
nkdraw\spt_values_pipe.txt -T -c -t"|"

Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000

2506 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (2506000.00 rows per sec.)

P:\SQLDenis>

In general I run bcp from within SSMS and use xp_cmdshell to do it.

Transfering a whole table into a file

To transfer a whole table into a file you need to specify the table name and also the direction, in this case it is out. The T switch specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. The security credentials of the network user, login_id, and password are not required. If –T is not specified, you need to specify –U and –P to successfully log in. The c switch performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \r\n (newline character) as the row terminator.

The command below will create a file named spt_values.txt in my C:\Junkdraw folder and it will use TAB as the delimiter

  1. exec xp_cmdshell 'bcp master.dbo.spt_values out C:\Junkdraw\spt_values.txt -T -c'


while that runs you will see the following output in your SQL Server Management Studio

NULL
Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
NULL
2506 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 78     Average : (32128.21 rows per sec.)
NULL


Transfering data into a file by using a query

In order to use a query you need to use queryout instead of out and you also need to use parentheses around the query itself Here is an example that uses a query

  1. exec xp_cmdshell 'bcp "select name,number,type from master.dbo.spt_values" queryout C:\Junkdraw\spt_values2.txt -T -c'


If you want to use a comma as a field delimiter you need to use the t switch with a comma, like this -t,

  1. exec xp_cmdshell 'bcp "select name,number,type from master.dbo.spt_values" queryout C:\Junkdraw\spt_values_comma.txt -T -c -t,'


If you want to use a pipe (|) as a field delimiter you need to use the t switch with "|", like this -t"|"

  1. exec xp_cmdshell 'bcp "select name,number,type from master.dbo.spt_values" queryout C:\Junkdraw\spt_values_pipe.txt -T -c -t"|"'


Using SQL authentication

The code shown until now used trusted connection using integrated security. We did that by using the T switch, here is what books on line has about the T switch

Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. The security credentials of the network user, login_id, and password are not required. If –T is not specified, you need to specify –U and –P to successfully log in.


In order to use a login and a password we need to use the U and P switches. So if my login is SQLDenis and my password is Wasabi, the command would now look like this

  1. EXEC xp_cmdshell 'bcp "select name,number,type from master.dbo.spt_values" queryout C:\Junkdraw\spt_values_pipe.txt -USQLDenis -PWasabi -c -t"|"'



The bcp tool is very powerful and has many switches that you can use, there are many more options, the full list can be found here: http://msdn.microsoft.com/en-us/library/ms162802.aspx


Contributed by: --SQLDenis 19:40, 15 April 2010 (GMT)


Part of SQL Server Admin Hacks

717 Rating: 2.8/5 (50 votes cast)