Skip to content

Latest commit

 

History

History
85 lines (56 loc) · 7.97 KB

prepare-data-for-bulk-export-or-import-sql-server.md

File metadata and controls

85 lines (56 loc) · 7.97 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords monikerRange
Prepare data for bulk export or import
This article describes how to plan bulk import and bulk export operations, including data file format requirements, and when to use the bcp utility.
rwestMSFT
randolphwest
12/20/2023
sql
data-movement
conceptual
bulk importing [SQL Server], planning
bulk importing [SQL Server], from a CSV file
data formats [SQL Server], planning operations
CSV files [SQL Server]
quoted fields in CSV files [SQL Server]
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current

Prepare data for bulk export or import

[!INCLUDE SQL Server Azure SQL Database Synapse Analytics PDW]

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

Note

If you're uncertain about how to format a data file for bulk importing, 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.

  • [!INCLUDE ssNoVersion] can use parallel scans to retrieve data. Therefore, the table rows that are bulk exported in from an instance of [!INCLUDE ssNoVersion] aren't ordinarily guaranteed to be in any specific order in the data file. To make 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.

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 doesn't need to be identical to the structure of the [!INCLUDE ssNoVersion] 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 can't be loaded into a datetime column. For more information, see Data formats for bulk import or bulk export (SQL Server) and Specify compatibility data formats when using bcp (SQL Server).

    [!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, use a format file. For more information, see XML Format Files (SQL Server).

  • Starting with [!INCLUDE sssql17-md], a CSV file can be used as the data file for a bulk import of data into [!INCLUDE ssNoVersion]. The field terminator of a CSV file doesn't have to be a comma. To be usable as a data file for bulk import, a CSV file must comply with the following restrictions:

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

  • 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 [!INCLUDE ssNoVersion] process (if the user logs in using [!INCLUDE ssNoVersion] provided login) or by the [!INCLUDE msCoName] 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.

Related content