Some Gotchas to Remember

I was surprised a few times during testing. First, importing the data from the fixed-field format file took considerably longer than importing the same data from the Comma Separated Values (CSV) format file. My second surprise came when I realized how complex minimally logging bulk copy operations was. To avoid these and other surprises, be aware of the following:

1. Know about table dependencies (e.g., foreign-key relationships) before the transfer. You might need to drop the constraint, import the data, then recreate the constraint.
2. When possible, remember to turn off logging and meet all the criteria to achieve minimal logging.
3. Data Transformation Services (DTS) isn't downward-compatible from SQL Server 2000 to SQL Server 7.0. DTS packages that you create with SQL Server 2000 won't run on SQL Server 7.0 and will cancel with error messages such as Invalid Class String or Parameter is incorrect.
4. You can import data files created with pre-SQL Server 2000 versions of bulk copy program (bcp) that contain SQL Server internal data formats or date formats that SQL Server 2000 doesn't support, but you must use SQL Server 2000's bcp utility with the -v compatibility switch.
5. By default, bulk copy operations don't execute triggers. The FIRE_TRIGGERS bulk copy hint lets SQL Server execute triggers once for each transaction during bulk copy import operations.
6. When you use bcp to bulk copy data into a view, the usual rules about adding rows to a view apply (e.g., you can insert fields into only one of the view's underlying tables). However, bcp will ignore default values, unspecified fields will get NULLs, and the insert will fail if you have defined the field as NOT NULL.
7. Operations that bulk copy data into a SQL Server table use transaction log space, even if the operations meet all the conditions for minimal logging. You should manage transaction log space during very large bulk copy operations by employing a judicious use of backups, the simple recovery model to allow log truncation on checkpoint, and a BATCHSIZE specification to segment the operation into multiple transactions.