BCP Basics
In this blog post, I’m going to walk through the basics of BCP (bulk copy program). BCP is a utility that installs with SQL Server and can assist with large data transfers.
Let’s see what parameter options are available to use. From the command line on a machine with SQL Server installed, type “bcp” and press Enter.
You can find out more information on BCP parameters on Books Online: http://msdn.microsoft.com/en-us/library/ms162802.aspx
For now, we’re going to examine just the basics. The simplest syntax of a BCP command is:
bcp
databaseName.Schema.TableName *or* “Query”
in, out, *or* queryout
-S ServerName\instanceName
-U userName -P password *or* -T
-c *or* -n *or* specify storage information for each column
Let’s look at these options in a little more detail:
databaseName.Schema.TableName *or* Query
You can specify either an entire table to copy or a query. The query should be surrounded in quotations and must also include the fully qualified table name.
in, out, *or* queryout
in = import, out = full table export, queryout = query to select data for export
-U userName -P password *or* -T
You can either specify a specific account to access SQL Server, or use -T to indicate Trusted Connection (i.e. Windows Authentication)
-c *or* -n *or* specify storage information for each column
-c indicates character data type, -n indicates native data type; if neither one is specified, by default you will be prompted for the data type for each column.
Now let’s put this together and run some BCP commands. All of these examples will use the AdventureWorks 2008 sample database.
First, let’s export an entire table. To do this, we’ll use the “out” parameter.
bcp AdventureWorks.Sales.SalesOrderDetail out C:\bcp_outputTable.txt -SYourServerName -T -c
I don’t normally export an entire table… or at least, not in one process. So let’s walk through what it would look like to export the same table using a query. This will use the “queryout” parameter.
bcp "Select SalesOrderID, SalesOrderDetailID, OrderQty, ProductID From AdventureWorks.Sales.SalesOrderDetail" queryout C:\bcp_outputQuery.txt -SYourServerName -T -c
You’ll notice that the total duration for the query was shorter than for the full-table export. This is because we’re only exporting a few of the columns. This is important to keep in mind when bcp’ing data: you’ll get better performance if you only export the data elements that you actually need.
Now that we’ve exported some data, let’s walk through the process of importing this data. First, let’s create a table with a constraint that will result in some errors.
CREATE TABLE dbo.testBCPLoad ( SalesOrderID INT Not Null , SalesOrderDetailID INT Not Null , OrderQty SMALLINT Null , ProductID INT Null CONSTRAINT PK_testBCPLoad PRIMARY KEY CLUSTERED (SalesOrderID) );
Now execute the BCP import command:
bcp sandbox.dbo.testBCPLoad in C:\bcp_outputQuery.txt -SYourServername -T -c
You should receive a Primary Key error. When you check your results in SQL Server, you should find no results loaded into the table. This is BCP’s default behavior.
Let’s change our constraint and try the same BCP command again:
ALTER TABLE dbo.testBCPLoad DROP CONSTRAINT PK_testBCPLoad; ALTER TABLE dbo.testBCPLoad ADD CONSTRAINT PK_testBCPLoad PRIMARY KEY CLUSTERED (SalesOrderID, SalesOrderDetailID);
bcp sandbox.dbo.testBCPLoad in C:\bcp_outputQuery.txt -SYourServername -T -c
You should now have the data loaded into your SQL Server destination table:
So there you have it, the basics of BCP! 
A few BCP tips:
- BCP commands are case-sensitive!
- If you’re accessing the data across a WAN, perhaps via a VPN connection, try to remote desktop (mstsc) to the actual SQL Server to perform the BCP. If possible, keep the operation on the same local drive or even local network as the server; the less distance data needs to travel across a network, the faster BCP will perform.
- If you need to copy large amounts of data (i.e. >100mm rows), try breaking the data into smaller chunks. This will help if you have an error during BCP (i.e. a PK error can rollback the entire import operation by default, although there are options that can change this behavior). When working with partitioned tables, I find it very efficient to segregate the data imported/exported by partition.
- If you’re BCP’ing data into a new table, you can minimize impact on the server by waiting to create your indexes after all the data is loaded.
- I like to construct my queries in SSMS, then copy them to BCP. Since the command-line utility does not support copy and pasting, I create a text file with my BCP command in NotePad, then save the command as a .cmd. To execute, just call the .cmd file.
Bulk Inserts with XML
Filed under: Performance & Tuning, SQL Tips, T-SQL Scripts
Last week, I blogged about how to perform one-to-many inserts with table-valued parameters, a feature new in 2008. For those who do not yet have 2008 or will not have it in the near future, it may still be beneficial to use XML for bulk inserts.
Here’s a pretty simple example of how to accomplish this:
/* Create some tables to work with */ CREATE TABLE dbo.orders ( order_id INT IDENTITY(1,1) Not Null , orderDate DATETIME Not Null , customer_id INT Not Null CONSTRAINT PK_orders PRIMARY KEY CLUSTERED(order_id) ); CREATE TABLE dbo.orderDetails ( orderDetail_id INT IDENTITY(1,1) Not Null , order_id INT Not Null , lineItem INT Not Null , product_id INT Not Null CONSTRAINT PK_orderDetails PRIMARY KEY CLUSTERED(orderDetail_id) CONSTRAINT FK_orderDetails_orderID FOREIGN KEY(order_id) REFERENCES dbo.orders(order_id) ); Go /* Create a new procedure using an XML parameter */ CREATE PROCEDURE dbo.insert_orderXML_sp @orderDate DATETIME , @customer_id INT , @orderDetailsXML XML AS BEGIN SET NOCOUNT ON; DECLARE @myOrderID INT; INSERT INTO dbo.orders ( orderDate , customer_id ) VALUES ( @orderDate , @customer_id ); SET @myOrderID = SCOPE_IDENTITY(); INSERT INTO dbo.orderDetails ( order_id , lineItem , product_id ) SELECT @myOrderID , myXML.VALUE('./@lineItem', 'int') , myXML.VALUE('./@product_id', 'int') FROM @orderDetailsXML.nodes('/orderDetail') AS nodes(myXML); SET NOCOUNT OFF; END GO /* Call our stored procedure */ EXECUTE dbo.insert_orderXML_sp @orderDate = '2008-01-01' , @customer_id = 101 , @orderDetailsXML = '<orderDetail lineItem="1" product_id="123" /> <orderDetail lineItem="2" product_id="456" /> <orderDetail lineItem="3" product_id="789" /> <orderDetail lineItem="4" product_id="246" /> <orderDetail lineItem="5" product_id="135" />'; /* Check our data */ SELECT * FROM dbo.orders; SELECT * FROM dbo.orderDetails; /* Clean up our mess */ DROP PROCEDURE insert_orderXML_sp; DROP TABLE dbo.orderDetails; DROP TABLE dbo.orders;
I’ve found that this is more efficient when performing large parent/child inserts, i.e. 1 parent record to 100 child records. Keep in mind that there’s a point where doing an insert with XML is more expensive than using a traditional INSERT stored procedure. I haven’t run any tests yet to help define what that threshold is… more to come on this in the near future.
More on the Nodes() Method can be found here in Books Online: http://msdn.microsoft.com/en-us/library/ms188282(SQL.90).aspx
Update: I’ve just learned that the “value” keyword is case-sensitive. Apparently my code box plug-in was defaulting “value” to “VALUE.”
Here’s the error message you’ll get if you don’t have “value” in lower-case:
Cannot find either column “myXML” or the user-defined function or aggregate “myXML.VALUE”, or the name is ambiguous.
Performance Comparison of Singleton, XML, and TVP Inserts
Filed under: Performance & Tuning, SQL 2008, SQL Tips
As promised, today I took a look at the performance of bulk inserts using XML and Table-Valued Parameters. I also compared it against singleton inserts to show the value in the bulk-insert approach.
My tests were pretty simple: insert 100 records using each method. Each test was executed 10 times to ensure consistency. The duration was recorded in microseconds.
The goal was to compare the performance of the inserts. Because I was executing this entire test within SQL Server, I had to isolate only the actual insert transactions and ignore everything else, such as the loading of the data; that work would normally be performed by the calling application.
So without further ado… screenshots of the Profiler traces: (click to enlarge)
Single Insert Method
XML Method
Table-Valued Parameter Method
Summary
| Method | Avg CPU | Avg Reads | Avg Writes | Avg Duration (micro) |
| Singleton Method | 3 | 202 | 0 | 13378 |
| XML Method | 0 | 222 | 0 | 3124 |
| TVP Method | 1 | 207 | 0 | 780 |
As expected, both the XML and the TVP method performed significantly better than the single-insert method. As hoped, the table-valued parameter arguably performed the best of all 3.
Large Updates on Replicated Tables
Late last night, I executed a 70mm update on a replicated table. This was a somewhat low priority update, so the primary goal (aside from the data change) was to avoid any issues on the publishing and subscribing servers, including replication latency errors. I have performed many large updates in the past, but this was the first on a replicated table.
To minimize impact, one of our system DBA’s suggested the use of a replicated stored procedure. Basically, instead of replicating each of the 70mm updates as a singleton transaction, the replicated stored procedure is called on the subscribing server, which then performs the bulk update locally. This was my first time using it and it worked beautifully.
Another of my colleagues, Jeff M., suggested the use of a control table. Normally, I would just output the last affected ID and update the script manually. However, this also worked so well that it will be adopted in all future update scripts.
Using the following pseudo-script and a replicated stored procedure, I was able to execute the update on 70mm records in 11.5 hours with *zero* impact on the servers and replication. I could’ve probably increased the batch size and reduced the execution time even further, but as I mentioned, this was a low priority update, so there was no need to push it.
This process should work equally well on non-replicated updates; merely replace the Execute statement with the actual update.
/************ Chunked Update Script with Control Table ************/ /* --------------------- Preparation Script --------------------- */ /* Note: Execute this section in a separate query window */ USE yourDatabase; Go SET NOCOUNT ON; /* Create a control table to facilitate tweaking of parameters */ CREATE TABLE dbo.scratch_largeUpdate_control ( sizeOfBatch INT , waitForDelay CHAR(8) , minRecordToUpdate INT , maxRecordToUpdate INT ); /* Create your control data; you only want 1 row in this table */ INSERT INTO dbo.scratch_largeUpdate_control (sizeOfBatch, waitForDelay, minRecordToUpdate, maxRecordToUpdate) SELECT 10000, '00:00:05', 40297132, 107459380; /* Update Script */ UPDATE dbo.scratch_largeUpdate_control SET sizeOfBatch = 100000 , waitForDelay = '00:00:30'; /* ------------------------ Update Script ------------------------ */ USE yourDatabase; Go SET NOCOUNT ON; DECLARE @batchSize INT , @minID INT , @maxID INT , @procMinID INT , @procMaxID INT , @delay CHAR(8) , @statusMsg VARCHAR(1000); BEGIN Try IF @@SERVERNAME Not In ('PRODSERVER') RAISERROR('Sorry, this cannot be executed here!', 16, 1); IF Not Exists( SELECT OBJECT_ID FROM sys.objects WHERE [name] = 'scratch_largeUpdate_control' And type = 'U' ) RAISERROR ('ERROR: Control table does not exist!', 16, 1) WITH NoWait; ELSE SELECT @minID = minRecordToUpdate , @maxID = maxRecordToUpdate , @batchSize = sizeOfBatch , @delay = waitForDelay FROM dbo.scratch_largeUpdate_control WITH (NoLock); SET @statusMsg = 'Beginning update; batch size of ' + CAST(@batchSize AS VARCHAR(10)) + ', delay of ' + @delay + ' defined. Estimate ' + CAST((((@maxID - @minID) / @batchSize) + 1) AS VARCHAR(10)) + ' iterations to be performed.' RAISERROR (@statusMsg, 10, 1) WITH NoWait; WHILE @minID < @maxID BEGIN SELECT @procMinID = @minID , @procMaxID = (@minID + (@batchSize - 1)); /* Execute actual update code here OR Call a replicated stored procedure, i.e. */ EXECUTE dbo.myReplicatedUpdateProc @minRecordID = @procMinID , @maxRecordID = @procMaxID; SET @statusMsg = 'Updating records ' + CAST(@minID AS VARCHAR(10)) + ' through ' + CAST((@minID + (@batchSize - 1)) AS VARCHAR(10)) + '...'; RAISERROR (@statusMsg, 10, 1) WITH NoWait; /* Update our control table with the last successfully updated record ID. In the event of an error, we can start from here. */ UPDATE dbo.scratch_largeUpdate_control SET minRecordToUpdate = @minID + @batchSize; SELECT @minID = @minID + @batchSize; WAITFOR Delay @delay; -- breather for the server /* Check to see if our control values have changed */ IF Not Exists( SELECT * FROM dbo.scratch_largeUpdate_control WITH (NoLock) WHERE @batchSize = sizeOfBatch And @delay = waitForDelay) BEGIN /* There was a change, so grab our new values */ SELECT @batchSize = sizeOfBatch , @delay = waitForDelay FROM dbo.scratch_largeUpdate_control WITH (NoLock) /* Print a status message with the new values */ SET @statusMsg = 'Parameters changed: batch size = ' + CAST(@batchSize AS VARCHAR(10)) + ', delay = ' + @delay; RAISERROR (@statusMsg, 10, 1) WITH NoWait; END END RAISERROR ('Success!', 10, 1) WITH NoWait; END Try /* Handle your errors */ BEGIN Catch SET @statusMsg = 'An error has occurred and the last ' + 'transaction has been rolled back. ' + 'Last record successfully updated was ' + 'record_id = ' + CAST((@minID + (@batchSize - 1)) AS VARCHAR(10)); RAISERROR (@statusMsg, 16, 1) WITH NoWait; /* Return the error message */ SELECT Error_Number() , Error_Procedure() , DB_NAME() , Error_Line() , Error_Message() , Error_Severity() , Error_State(); END Catch; /* -------------------------- Clean-Up ---------------------------- Drop Table dbo.scratch_largeUpdate_control; ----------------------------------------------------------------- */
Subscribe to my blog!
It's easy, free, and will keep you informed of my latest SQL tips and scripts.Like what you see? Subscribe! 
Around the Web
SQLRockstar Blog Ranking
