Share via

Preparing Data for Bulk Export or Import

  • Article

This section discusses the considerations involved in planning for bulk-export operations and the requirements for bulk-import operations.

Note

If you are not sure about how to format a data file for bulk importing, you can use the bcp utility to export data from the table into a data file. The formatting of each data field in this file shows the formatting required to bulk import data into the corresponding table column. Use the same data formatting for fields of your data file.

Data-File Format Considerations for Bulk Export

Before you perform a bulk-export operation by using the bcp command, consider the following:

  • When data is exported to a file, the bcp command creates the data file automatically by using the specified file name. If that file name is already in use, the data that is being bulk copied to the data file overwrites the existing contents of the file.
  • Bulk export from a table or view to a data file requires SELECT permission on the table or view that is being bulk copied.
  • Microsoft SQL Server can use parallel scans to retrieve data. Therefore, the table rows that are bulk exported in from an instance of SQL Server are not ordinarily guaranteed to be in any specific order in the data file. To make sure that bulk-exported table rows appear in a specific order in the data file, use the queryout option to bulk export from a query, and specify an ORDER BY clause. For more information, see Exporting Data from a Query to a Data File.

Data-File Format Requirements for Bulk Import

To import data from a data file, the file must meet the following basic requirements:

  • The data must be in row and column format.

Note

The structure of the data file does not need to be identical to the structure of the SQL Server table because columns can be skipped or reordered during the bulk-import process.

  • The data in the data file must be in a supported format such as character or native format.

  • The data can be in character or native binary format including Unicode.

  • To import data by using a bcp command, BULK INSERT statement, or INSERT ... SELECT * FROM OPENROWSET(BULK...) statement, the destination table must already exist.

  • Each field in the data file must be compatible with the corresponding column in the target table. For example, an int field cannot be loaded into a datetime column. For more information, see Using Native, Character, or Unicode Formats and Specifying Data Formats for Compatibility by Using bcp.

    Note

    To specify a subset of rows to import from a data file rather than the entire file, you can use a bcp command with the -F first_row switch and/or -L last_row switch. For more information, see bcp Utility.

  • To import data from data files that have fixed-length or fixed-width fields, you must use a format file. For more information, see Sample XML Format Files.

  • In some cases, a comma-separated value (CSV) file can be used as the data file for a bulk import of data into SQL Server. Note that the field terminator of a CSV file does not have to be a comma. To be usable as a data file for bulk import, a CSV file must comply with the following restrictions:

    • Data fields never contain the field terminator.
    • Either none or all of the values in a data field are enclosed in quotation marks ("").
      To import data from a Microsoft FoxPro or Visual FoxPro table (.dbf) file or a Microsoft Excel worksheet (.xls) file, convert the data into a .CSV file, whose file extension will typically be .csv. You can then use the .csv file as a data file in a SQL Server bulk-import operation.

In addition, the bulk import of data from a data file into a table requires the following:

  • Users must have INSERT and SELECT permissions on the table. Users also need ALTER TABLE permission when they use options that require data definition language (DDL) operations, such as disabling constraints.
  • When you bulk import data by using BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK...), the data file must be accessible for read operations by either the security profile of the SQL Server process (if the user logs in using SQL Server provided login) or by the Microsoft Windows login that is used under delegated security. Additionally, the user must have ADMINISTER BULK OPERATIONS permission to read the file.

Note

Bulk importing into a partitioned view is unsupported, and attempts to bulk import data into a partitioned view fail.

See Also

Concepts

Using Character Format to Import or Export Data
Using Native Format to Import or Export Data
Understanding XML Format Files

Other Resources

bcp Utility
BULK INSERT (Transact-SQL)
Data Types (Transact-SQL)
ExportData Method
ImportData Method

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

15 September 2007
Changed content:
  • Expanded the discussion of preparing a CSV file for bulk import.

17 July 2006
New content:
  • Added a Note to the introduction.
  • Added a requirement about how to import fixed-length or fixed-width fields.

14 April 2006
New content:
  • Added information about the data-file format when importing data from outside SQL Server.