Editorials

Data Import Tips

by Ben Taylor

Have you ever wanted to import a lot of data into your SQL Server database quickly? Sometimes you have a lot of data, and your system spends a lot of time allocating disk space for new data, or maintaining a transaction log for the work being done. So, here are some options available to you when bringing in a lot of data.

  1. Drop unnecessary indexes until the data is imported, and then recreate them. This allows the work to be done more quickly, and can help keep the indexes and data from being fragmented.
  2. Use the ADO Bulk Insert technique.
  3. Use the TSQL Bulk Insert command. This brings the data in with minimal transaction logging.
  4. Use the BPC command from a command prompt. This allows you to control a lot of different parameters, and also provide a format file to define the structure of the data being imported. The format file may be used with the
    TSQL Bulk Insert command as well.
  5. Make sure your database has already allocated enough disk space on the data file where the import will be stored. Incrementally allocating space during import takes more time than allocating the space ahead of time.
  6. Use batches to break up your data into smaller imports when working with very large amounts of data. If there are logical units within the data, that can often be a useful batch separation.

Cheers,

Ben

Tags:
Author: Ben TaylorBen is a Relational Database veteran starting in the early eighties on DEC Vax database systems such as Bloom and Harvest. He has specialized in SQL engines for more than two decades, with a primary focus on Microsoft SQL Server. Ben has worked with small and large databases for both OLAP and OLTP working on systems for many industries such as Medical (Medical Claims and Eligibility Warehouse, HMO Provider Management, Enrollment), Financial (credit card processing, mortgage, stock portfolio management), Manufacturing (Assembly Line and Product Assemblies), Sales, Auto Auctions and more.