Home arrow SQL Server arrow Page 4 - An Introduction To The Bulk Copy Utility

An Introduction To The Bulk Copy Utility

There are a number of ways to import and export data with SQL Server 2000. Bulk copy is one of them. In this article Mitchell introduces us to basic import/export functionality with the bulk copy command utility.

Author Info:
By: Mitchell Harper
Rating: 4 stars4 stars4 stars4 stars4 stars / 98
June 12, 2002
  1. · An Introduction To The Bulk Copy Utility
  2. · What is bulk copy?
  3. · Using the bcp.exe utility
  4. · Bcp.exe Example #1
  5. · Conclusion

print this article

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:

The nw_products.txt 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:

The shippers table before running bcp.exe

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:

The shippers table after running bcp.exe

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.
blog comments powered by Disqus

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials