Copying Data from a File to SQL Server

To copy a file to SQL Server, follow these guidelines:

• When copying data from a file into a table that has no indexes, be sure that the Select Into/Bulk Copy option has been set to true. The SA or database owner can set this option with SQL Enterprise Manager (or the sp_dboption system procedure).
• After copying data from a file, back up the database with the DUMP DATABASE statement so that the copy and any subsequent changes can be recovered.

The following illustration shows the process to follow to copy data into SQL Server at maximum speed.

    To copy data from a file to SQL Server

1. Use SQL Enterprise Manager (or sp_dboption) to set the Select Into/Bulk Copy option for that database to true.
2. Drop the indexes on the table. (This can be performed by table owner.)
3. Be sure that you have select and insert permissions on the table. (The table owner can assign select and insert permissions.)
4. Perform the copy with bcp. (This can be performed by any user with select and insert permissions.)
5. Use SQL Enterprise Manager (or sp_dboption) to set the Select Into/Bulk Copy option for that database to false.
6. Use DUMP DATABASE to back up the newly inserted data. (This can be performed by the SA or the database owner.)
7. Re-create the indexes. Check to make sure that you have enough space. The distribution statistics for indexes are automatically updated.
8. Execute stored procedures or queries to check if any of the newly loaded data violates rules or triggers. (This can be performed by the table owner or the stored procedure owner.)

When the transfer is complete, the program reports the number of rows successfully copied and some performance information.