Home > SQL Tips > BCP { IN | OUT | QUERYOUT } – the BCP util

BCP { IN | OUT | QUERYOUT } – the BCP util


As per MS BOL the BCP utility bulk copies data between an instance of SQL Server and a data file in a user-specified format. The BCP utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.

BCP or Bulk Copy Program is a command line utility that runs on DOS console. It is not a SQL statement, to run it from SQL SSMS you must use the xp_cmdshell Extended Stored Procedure as shown below.

Now let’s check with few examples how we can use the BCP Utility:

--// BCP OUT
-- Bulk Copy All Table records to a File
SELECT *
FROM Person.Address

exec master..xp_cmdshell 'BCP AdventureWorks.Person.Address OUT d:\PersonAddressAll.txt -T -c'
-- Processing status on Result Pane
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
...
1000 rows successfully bulk-copied to host-file. Total received: 18000
1000 rows successfully bulk-copied to host-file. Total received: 19000
NULL
19614 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 250 Average : (78456.00 rows per sec.)
NULL
--// BCP QUERYOUT
-- Bulk Copy Selected (Top 20) records by using Query in BCP command from Table to a file
SELECT TOP 20 AddressID, AddressLine1, City, StateProvinceID, PostalCode, ModifiedDate
FROM Person.Address

exec master..xp_cmdshell 'BCP "SELECT TOP 20 AddressID, AddressLine1, City, StateProvinceID, PostalCode, ModifiedDate FROM AdventureWorks.Person.Address" QUERYOUT d:\PersonAddressByQuery.txt -T -c'
-- Processing status on Result Pane
NULL
Starting copy...
NULL
20 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (20000.00 rows per sec.)
NULL
--// BCP IN
-- Bulk Copy All File records to an existing Table
-- Create a similar table first
IF OBJECT_ID('AdventureWorks.Person.Address2') IS NOT NULL
DROP TABLE AdventureWorks.Person.Address2

SELECT *
INTO AdventureWorks.Person.Address2
FROM AdventureWorks.Person.Address
WHERE 1=2

exec master..xp_cmdshell 'BCP AdventureWorks.Person.Address2 IN d:\PersonAddressAll.txt -T -c'

-- Check newly inserted records
SELECT *
FROM AdventureWorks.Person.Address2 -- (19614 row(s) affected)

Options used in above queries:
-T : Trusted connection, here username & password are not required.
-c : Performs the operation using a character data type.

Note: In case of no Trusted connection, you must have to use the -U & -P options, like: “-U username -P password” instead of “-T”. If “-P password” is not applied the password is asked when the command is submitted, thus it is also a secure way of entering a masked password.
If you are not on default instance than there will be need to provide the Server instance name also by adding -S option, like: “-S ServerName”.

About these ads
  1. Nian
    October 4, 2013 at 2:31 pm

    Very nice. Thanks.

  2. Vivek
    February 10, 2014 at 12:15 pm

    not Working
    SET @bcpCommand=’BCP “select top 1 * from RG_Reportprocessing.dbo.RG_WholeSalerBookingParityAnalysisData where website=”website”
    union
    select * from RG_Reportprocessing.dbo.RG_WholeSalerBookingParityAnalysisData where website !=”website”” QUERYOUT D:\WholesalerChecks\Production\ReportFiles\’ + @ParityFile + ‘ -T -c, -U sa -P password -S MainServer’
    EXECUTE MASTER.DBO.XP_CMDSHELL @bcpCommand

    • February 10, 2014 at 12:35 pm

      @Vivek, what error you are getting?

  1. September 13, 2011 at 5:21 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 401 other followers

%d bloggers like this: