Summary

This chapter covered different ways to invoke Query Editor from within SQL Server Management Studio and then touched on the new capabilities of Query Editor in SQL Server 2005. It then described the architectural details of the Query Editor data retrieval mechanism and finished up with the new XML data type support.

Both BCP and Bulk Insert commands provide very similar functionality for an import operation. However, there are some subtle differences you may want to consider. BCP is a client application that typically runs on a computer other than Database Engine. So the work to read and parse the data file and to convert the data into native format is done on the client machine. It sends data over a TDS stream, which can be an issue on a slow network. Bulk Insert, on the other hand, runs as part of the Database Engine process. It thereby uses Database Engine resources to read and parse the data file. Neither of these operations provide any transformation (e.g., aggregate) of the data during import. You can categorize these options under ETL (extract, transform, and load) with a missing Tthat is, no transformation of data. If you need to do some data transformation before the import, you may want to consider OPENROWSET with the BULK rowset provider for simple transformations and SSIS for complex transformations.