Printed 2017/01/18 06:24AM

bcp Utility


There was a time that I avoided bcp utility like the plague. A few years ago, we needed to set up a once a month job to transfer about 100 GB of data from one server to another. Since this involved two SQL Server 2000 instances, the first attempt was using DTS, but the performance was just not good enough. We considered setting up a job to copy a backup file from one server to another, do a restore and then import the data. I decided to give bcp a try, exporting the data to files and then importing the data into the required tables. The performance was acceptable and the bcp solution has been in place without any issues since then.

I think that the reason I avoided using bcp for so long is that it has a dizzying number of options. Luckily, I found some bcp scripts written by a vendor using just a handful of arguments that got me started. Since then I have found several opportunities to use bcp and now consider it a very useful tool.

The bcp utility is a command line tool. You can also use it from the Query Window if xp_cmdshell is enabled. One thing to keep in mind is that when it is used from the command window or batch file, the file path is in the context of the box where the command is run. When running from the Query Window or a stored proc, the file path will be in the context of the server.

Below is an example script that I ran from my laptop at home in the Query Window. It is running SQLExpress. There are many, many more options available for bcp, but this minimal number of arguments has worked for just about everything I have wanted to do. There is also a handy “queryout” argument you can use instead of “out” to use a select statement instead of a table or view name.

You will have to modify the server name and file path for your environment. Make sure that the bcp command ends up all on one line.

Export from the AdventureWorks database to a file
	out = export to a file
	-S = Server\Instance
	-T = trusted authentication
	-c = use character datatypes, tab delimited
	-t = override the delimiter

exec master.dbo.xp_cmdshell 'bcp AdventureWorks.HumanResources.Employee out c:\temp\employee.txt -S localhost\SQLExpress -T -c -t "|"'

--Create a blank table
use AdventureWorks
if object_id('dbo.test_import') is not null
	drop table dbo.test_import

select * into dbo.test_import from HumanResources.Employee where 1 = 2

Import from a file into a table. 
	in = import from a file
	-S = Server\Instance
	-T = trusted authentication
	-c = character datatypes, tab delimited
	-t = override the delimiter

exec master.dbo.xp_cmdshell 'bcp AdventureWorks.dbo.test_import in c:\temp\employee.txt -S localhost\SQLExpress -T -c -t "|"'

select * from dbo.test_import

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.