Adding Rows by Using Bulk Copy Operations
The Microsoft SQL Server 2005 bulk copy components support the following operations:
- Inserting numbers of rows into a table or view.
- Retrieving large numbers of rows from a table, view, or query.
Bulk copy is the fastest way to add large numbers of rows in SQL Server. There are three ways to run bulk copy operations:
- Use the bulk copy program (the bcp utility).
bcp is a command prompt utility. bcp provides for running bulk copies in .bat and .cmd scripts. bcp is used to bulk copy large files into tables or views in SQL Server databases. For more information, see Importing and Exporting Bulk Data by Using the bcp Utility. - Use the BULK INSERT statement in Transact-SQL batches, stored procedures, and triggers to bulk import data from a file into a table or view in a SQL Server database.
The BULK INSERT statement is executed on the server in the context of the SQL Server (MSSQLServer) service, not on the client. If the file being bulk copied is also on the server the data is not moved across the network at all. For more information, see Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...). - Use an INSERT ... SELECT * FROM OPENROWSET(BULK...) Transact-SQL statement to bulk import data from a data file into a SQL Server table. For more information, see Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) and OPENROWSET.
- Use the bulk copy APIs for OLE DB, ODBC, and DB-Library applications.
The bcp utility is an ODBC command prompt utility that uses the SQL Server ODBC driver bulk copy functions. Any application can use these published bulk copy functions in ODBC or DB-Library applications to run bulk copy operations. Applications can bulk copy from files into a SQL Server table or view. Applications can also bulk copy from program variables into a SQL Server table or view. For more information about OLE DB bulk copies, see Performing Bulk Copy Operations. For more information about ODBC bulk copies, see Performing Bulk Copy Operations (ODBC)Performing Bulk Copy Operations (ODBC).
See Also
Concepts
Adding a Row by Using a Result Set Position
Adding ntext, text, or image Data to Inserted Rows
Adding Rows by Using INSERT and SELECT
Importing and Exporting Bulk Data
Other Resources
bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)