I-Tech SQL Server Script Series – Generating BCP Commands

BCP (bulk copy program) is a command-line tool provided with SQL Server for quickly loading and extracting data. BCP extracts or loads data to / from a single table (or from a query) and is very much like a table-level backup file. Not as many developers are aware of BCP since DTS and now SSIS have become available but it is still very useful for moving data around quickly. It’s great for moving files between environments without making a backup of the whole database and it’s also a good safety net before you make a major change to the data in a table. I use it frequently in development environments and I’ve received data-loads from vendors as BCP files because of their ease-of-use and speed of loaded and extracting.

BCP is very flexible and fast, but since it is a command-line tool it has a minimal interface. This can be intimidating to new SQL developers and it can also be tedious if you need to extract all the tables in a database. That’s where this script comes in – it generates the BCP statements that you can put into the command line. Just set the variables at the top of the script and it’s ready to go. Run the script from the database you are targeting. When you run the generated commands it creates or loads the BCP files from the current directory. You can easily add a specific directory to the script if that’s what you need.

SQL Script

SELECT 
    @SERVER          = @@SERVERNAME
    ,@EXTENSION      = 'BCP'
    ,@OPTIONS        = '-n -k -E'
    ,@SCHEMA         = NULL
    ,@TRUSTED        = 1
    ,@USER           = ''
    ,@PASSWORD       = ''
    ,@TYPE           = 'BOTH'	--IN,OUT,BOTH
    ,@INCLUDEDATE    = 1

You can see the options listed above. The -n saves / loads the file in native format (binary). This limits how you can load the file but you don’t need a separate format file with this option. The script also has -k, keep NULL values and -E, keep identity values. Change any of the values needed.

Output

Running the script gives you something like this:

bcp [AdventureWorks2012].[Person].[Address] OUT AdventureWorks2012.Person.Address_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Person].[AddressType] OUT AdventureWorks2012.Person.AddressType_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[dbo].[AWBuildVersion] OUT AdventureWorks2012.dbo.AWBuildVersion_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Production].[BillOfMaterials] OUT AdventureWorks2012.Production.BillOfMaterials_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Person].[BusinessEntity] OUT AdventureWorks2012.Person.BusinessEntity_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Person].[BusinessEntityAddress] OUT AdventureWorks2012.Person.BusinessEntityAddress_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Person].[BusinessEntityContact] OUT AdventureWorks2012.Person.BusinessEntityContact_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Person].[ContactType] OUT AdventureWorks2012.Person.ContactType_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Person].[CountryRegion] OUT AdventureWorks2012.Person.CountryRegion_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Sales].[CountryRegionCurrency] OUT AdventureWorks2012.Sales.CountryRegionCurrency_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Sales].[CreditCard] OUT AdventureWorks2012.Sales.CreditCard_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Production].[Culture] OUT AdventureWorks2012.Production.Culture_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Sales].[Currency] OUT AdventureWorks2012.Sales.Currency_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Sales].[CurrencyRate] OUT AdventureWorks2012.Sales.CurrencyRate_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Sales].[Customer] OUT AdventureWorks2012.Sales.Customer_20140804.BCP -n -k -E -T  -S .
...
...
...
bcp [AdventureWorks2012].[Production].[UnitMeasure] OUT AdventureWorks2012.Production.UnitMeasure_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Purchasing].[Vendor] OUT AdventureWorks2012.Purchasing.Vendor_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Production].[WorkOrder] OUT AdventureWorks2012.Production.WorkOrder_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Production].[WorkOrderRouting] OUT AdventureWorks2012.Production.WorkOrderRouting_20140804.BCP -n -k -E -T  -S .

Results

Running this is very fast. Paste this into a command / batch file or put it directly into a command prompt after changing to the target directory. Some sample output:

C:\Temp\AdventureWorks>bcp [AdventureWorks2012].[Production].[WorkOrderRouting]
OUT AdventureWorks2012.Production.WorkOrderRouting_20140804.BCP -n -k -E -T  -S
.

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: 3000
1000 rows successfully bulk-copied to host-file. Total received: 4000
1000 rows successfully bulk-copied to host-file. Total received: 5000
...
...
...
1000 rows successfully bulk-copied to host-file. Total received: 65000
1000 rows successfully bulk-copied to host-file. Total received: 66000
1000 rows successfully bulk-copied to host-file. Total received: 67000

67131 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1092   Average : (61475.27 rows per sec.)

Auto-generating the script makes using BCP even easier. If you need to save space you can zip / gzip the files and get decent compression depending on your database. I’ve seen 5:1 or 10:1 compression, but this varies based on the type of data and your specific tables.

Script
The script is located here – ADMIN.BCP.Create.In.Out

It is also located on our script page – I-Tech SQL Server Scripts

Leave a Reply

Your email address will not be published.