• Article

Preparing Data for Bulk Export or Import

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

Note

If you are uncertain 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 with fixed-length or fixed-width fields, you must use a format file. For more information, see Understanding XML Format Files and Sample XML Format Files.

  • Comma-separated value (CSV) files are not supported by SQL Server bulk-import operations. However, in some cases, a 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 bulk import data from a Microsoft FoxPro or Visual FoxPro table (.dbf) file or a Microsoft Excel worksheet (.xls) file, you would need to convert the data into a CSV file that complies to the preceding restrictions. The file extension will typically be .csv. You can then use the .csv file as a data file in a SQL Server bulk-import operation.

    On 32-bit systems, it is possible to import CSV data into a SQL Server table without bulk-import optimizations by using OPENROWSET with the OLE DB Provider for Jet. Jet treats text files as tables, with the schema defined by a schema.ini file that is located in the same directory as the data source. For a CSV data, one of the parameters in the schema.ini file would be "FORMAT=CSVDelimited". To use this solution, you would need to understand how the Jet Test IISAMm operations—its connection string syntax, schema.ini usage, registry setting options, and so on). The best sources of this information are Microsoft Access Help and Knowledge Base (KB) articles. For more information, see Initializing the Text Data Source Driver, How To Use a SQL Server 7.0 Distributed Query with a Linked Server to Secured Access Databases, HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases, and How To Open Delimited Text Files Using the Jet Provider's Text IIsam.   

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.