An Introduction To The Bulk Copy Utility - Bcp.exe Example #1
(Page 4 of 5 )
In this example we're going to use bcp.exe with a trusted connection to export all rows in the Northwind.Products table to an ASCII-character formatted file, c:\nw_products.txt:
bcp.exe northwind..products out "c:\nw_products.txt" –c -T Bcp.exe response with the following text, indicating a successful export:
Starting copy...
77 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 50
The resultant file contains plain-text rows extracted from the products table of the Northwind database. Each field is separated by a tab. Here's a sample from the file:
Bcp.exe Example #2 Let's modify our last example, and instead of selecting all rows from the products table into an ASCII-character file, this time we will export the result of a TSQL query in SQL Server's native binary format:
bcp.exe "select * from northwind..employees" queryout "c:\nw_employees.bin" –n –T Bcp.exe response with the following text, indicating a successful export:
Starting copy...
9 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 30
Notice how this time we've passed bcp.exe a TSQL query surrounded by double quotes, as well as the queryout switch, which tells bcp.exe that we want to export the results of a TSQL query. We've also used the –T switch, which means that the output file, nw_employees.bin will be SQL Server's proprietary binary data format.
Bcp.exe Example #3 For our last bcp.exe example, we will look at importing data from an export file into the database. Let's start by creating the export file from the values in Northwind's shipping table. Here's how the shipping table looks:
bcp.exe northwind..shippers out "c:\nw_shippers.bin" –T –n We now have the contents of the shippers table exported into SQL Server's native binary format as nw_shippers.bin. The next step is to import the data from the binary file back into the shippers table with the following bcp.exe syntax:
bcp.exe northwind..shippers in "c:\nw_shippers.bin" –T –n As you can see, it's exactly the same command, except that we use the in switch to tell bcp.exe that we are importing data from a file, instead of the out switch which specifies that we are exporting to a file.
Here's how the shippers table looks after our import:
As we can see, the rows that already existed are still preserved and the imported rows are added to the end of the table. ShipperID is an auto incremented primary key, and as the screenshot above shows, this identifier is preserved, even for the three new rows added from the import.
Next: Conclusion >>
More SQL Server Articles
More By Mitchell Harper
/ 77 
