Bulk Insert Best Practices

• Mark Resnicoff

  SSC Veteran

  Points: 200

  More actions

  April 6, 2007 at 5:43 am

  #119540

  I am consulting with a company that needs to insert data into their SQL Server 2005 database from a file with 4.5 million rows in it.  The file contains approximately 240 columns of data, and data is not needed for approximately 50-60 of those columns.

  This data will need to be inserted into multiple tables.  Also, approximately 10 columns need to be encrypted.  The encryption will be symmetric using a certificate.

  My question is what is the best/most efficient way to handle a bulk insert like this?  I want to keep the size of the transaction log to a minimum (and actually don't even need transaction logging for this).

  Is there a way to do all this in one shot via bulk insert, or would it be best to use a multi-stage approach, such as:

  1. Set database to single-user mode so that no one else has access to the database until encryption of the data is complete (see steps 2 and 3 below).

  2. Bulk insert the file into a temp table whose structure is the same as the data file layout.

  3. Create a stored proc to process the data, inserting the data into the correct tables and encrypting the necessary data as necessary.

  I would truly appreciate any help.

  Thanks,

  Mark