BCP – Still a Great Utility
Sybase shipped BCP decades ago as a command line tool to import and extract data from SQL Server. This tool continues to live on even in Microsoft SQL Server, and still is a convenient tool in many scenarios.
BCP as it was originally designed could extract data from tables and/or views into a file. You could format the file, or simply choose the native format. The data could also be imported into a table using the BCP Utility.
I have been using it recently to deal with some large data issues, as well as debugging. For large data, I had a table with many records, of which I wanted to delete 30%, 60% I wanted to delete some data in the rows, and the remaining 10% would remain the same. The net result would shrink the table by 66%.
I found it easier to extract the data I wished to retain using BCP and a view that performed the filter and data modification. I then Truncated the table which is near instantaneous. Finally I imported the modified data back into the table, and rebuilt the indexes to handle fragmentation. The net result was completing a process in about two hours that would have taken days to do in the table directly. Since I had an open window when the table was not modified I was able to take this approach.
Another occasion I wanted to copy a production record into my test database for some debugging purposes. One of the nice features Microsoft added to the command line BCP utility was the ability to export not only from a table or view, but also from a query. So, I write a query filtering my record in the BCP statement, exporting the data in the SQL native format. Once extracted, I can immediately BCP IN the data into my test database. Now I can test without having to perform a complete backup and restore.
There are many other ways to accomplish these same tasks. Still, I somehow come back to BCP when I need something fast and efficient.
Share your thoughts or comments below or in email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Planning for Recovery
In this article, you will learn recovery procedures for a catastrophic failure. You learn how to view and manage database states. You also learn how to troubleshoot and resolve disk space issues with tempdb.
Featured White Paper(s)
Optimizing analytic workloads using DB2 10.5 with BLU Acceleration
BLU Acceleration is a new collection of technologies for analytic queries that are introduced in DB2 for Linux, UNIX, and Win… (read more)
Featured Script
dba3_NorthWind_0080_Implement_Materialized_View_Invoices_Article
View it now! – Part II: Banishing view timeouts by implementing user "materialized views"… (read more)