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
go
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