SQL Server ships with a number of methods for importing and exporting data to and from SQL Server in bulk. The concept, as you probably know is called Bulk Copy.
- Bulk Copy ADO implementation
- Bulk_Insert TSQL command
- Bulk Copy Executable Utility
The Bulk Copy implantation in ADO works nicely when you have data you want to import from a Dot Net program. You can easily import data from a System.Data.DataTable object. It allows you to specify many different properties to be used when performing the Bulk Copy to optimize performance, and override things such as Identity column values. This is a great capability In C# or VB.Net. Since you can turn just about any IEnumerable collection into a DataTable, this implementation is quite flexible.
SQL Server added TSQL commands for importing data in bulk so that you can call it from within a stored procedure, or as an ad-hoc query. The Bulk Import command works much the same as the old executable BCP.exe program. You can provide a format file that tells the process how to transform the input source to the table, or from the table to an output source. There is a native option which uses a binary format for in and out, which is much faster.
Just like the ADO version, you can provide parameters to the BULK INSERT command, telling it how to handing things such as IDENTITY column values. You can also provide a first or last row to import if you are reading from a file.
One tool I always keep in mind, especially when transporting large volumes of data from one database to another, is the BCP.exe program. Like any console program, BCP.exe is parameter driven. You can provide a format file for converting source data to tables or tables to destination data. I find this implementation to be the fastest BULK COPY technique out of the three options when it comes to performance.
All of these BULK COPY implementations are minimally logged when importing to reduce the overhead of the import process. However, if you do import using BULK COPY, it is best if you perform a database backup after the process completes to maintain the integrity of your backup and restore history.
In conclusion, consider BULK COPY when you need to move large amounts of data into or out of a database, yet don’t require the entire database. Bulk Copy is table specific, with the exception that you can export from a view.
Cheers,
Ben