Что такое bulk insert

Use BULK INSERT or OPENROWSET(BULK. ) to import data to SQL Server

This article provides an overview of how to use the Transact-SQL BULK INSERT statement and the INSERT. SELECT * FROM OPENROWSET(BULK. ) statement to bulk import data from a data file into a SQL Server or Azure SQL Database table. This article also describes security considerations for using BULK INSERT and OPENROWSET(BULK. ), and using these methods to bulk import from a remote data source.

When you use BULK INSERT or OPENROWSET(BULK. ), it is important to understand how SQL Server version handles impersonation. For more information, see «Security Considerations,» later in this topic.

BULK INSERT statement

BULK INSERT loads data from a data file into a table. This functionality is similar to that provided by the in option of the bcp command; however, the data file is read by the SQL Server process. For a description of the BULK INSERT syntax, see BULK INSERT (Transact-SQL).

BULK INSERT examples

OPENROWSET(BULK. ) Function

The OPENROWSET bulk rowset provider is accessed by calling the OPENROWSET function and specifying the BULK option. The OPENROWSET(BULK. ) function allows you to access remote data by connecting to a remote data source, such as a data file, through an OLE DB provider.

To bulk import data, call OPENROWSET(BULK. ) from a SELECT. FROM clause within an INSERT statement. The basic syntax for bulk importing data is:

When used in an INSERT statement, OPENROWSET(BULK. ) supports table hints. In addition to the regular table hints, such as TABLOCK, the BULK clause can accept the following specialized table hints: IGNORE_CONSTRAINTS (ignores only the CHECK constraints), IGNORE_TRIGGERS, KEEPDEFAULTS, and KEEPIDENTITY. For more information, see Table Hints (Transact-SQL).

For information about additional uses of the BULK option, see OPENROWSET (Transact-SQL).

Security considerations

If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service).

To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data. In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.

For example, consider a user who logged in to an instance of SQL Server by using Windows Authentication. For the user to be able to use BULK INSERT or OPENROWSET to import data from a data file into a SQL Server table, the user account requires read access to the data file. With access to the data file, the user can import data from the file into a table even if the SQL Server process does not have permission to access the file. The user does not have to grant file-access permission to the SQL Server process.

SQL Server and Microsoft Windows can be configured to enable an instance of SQL Server to connect to another instance of SQL Server by forwarding the credentials of an authenticated Windows user. This arrangement is known as impersonation or delegation. Understanding how SQL Server version handle security for user impersonation is important when you use BULK INSERT or OPENROWSET. User impersonation allows the data file to reside on a different computer than either the SQL Server process or the user. For example, if a user on Computer_A has access to a data file on Computer_B, and the delegation of credentials has been set appropriately, the user can connect to an instance of SQL Server that is running on Computer_C, access the data file on Computer_B, and bulk import data from that file into a table on Computer_C.

Bulk importing to SQL Server from a remote data file

To use BULK INSERT or INSERT. SELECT * FROM OPENROWSET(BULK. ) to bulk import data from another computer, the data file must be shared between the two computers. To specify a shared data file, use its universal naming convention (UNC) name, which takes the general form, \\Servername\Sharename\Path\Filename. Additionally, the account used to access the data file must have the permissions that are required for reading the file on the remote disk.

This restriction does not apply to the bcp utility because the client reads the file independently of SQL Server.

Bulk importing from Azure Blob storage

When importing from Azure Blob storage and the data is not public (anonymous access), create a DATABASE SCOPED CREDENTIAL based on a SAS key which is encrypted with a MASTER KEY, and then create an external database source for use in your BULK INSERT command.

Do not use explicit transaction, or you receive a 4861 error.

Using BULK INSERT

The following example shows how to use the BULK INSERT command to load data from a csv file in an Azure Blob storage location on which you have created a SAS key. The Azure Blob storage location is configured as an external data source. This requires a database scoped credential using a shared access signature that is encrypted using a master key in the user database.

Azure SQL Database does not support reading from Windows files.

Using OPENROWSET

The following example shows how to use the OPENROWSET command to load data from a csv file in an Azure Blob storage location on which you have created a SAS key. The Azure Blob storage location is configured as an external data source. This requires a database scoped credential using a shared access signature that is encrypted using a master key in the user database.

Azure SQL Database does not support reading from Windows files.

Источник

задача «Массовая вставка»

Чтобы обеспечить высокую скорость копирования, данные не должны подвергаться преобразованиям при перемещении из исходного файла в таблицу или представление.

Особенности использования

Перед использованием задачи «Массовая вставка» примите во внимание следующее.

Запускать пакеты, содержащие задачу «Массовая вставка», могут только члены предопределенной роли сервера sysadmin.

Использование задачи «Массовая вставка» вместе с транзакциями

Если размер пакета не указан, вся операция массового копирования рассматривается как одна транзакция. Размер пакета 0 указывает, что все данные вставлены в один пакет. Если указан размер пакета, каждый пакет представляет собой транзакцию, которая фиксируется после завершения работы пакета.

Поведение задачи «Массовая вставка» по отношению к транзакциям зависит от того, присоединяется ли задача к транзакции пакета. Если задача «Массовая вставка» не присоединена к транзакции пакета, каждый пакет без ошибок фиксируется как отдельный модуль перед попыткой передачи следующего пакета. Если задача «Массовая вставка» присоединена к транзакции пакета, пакеты без ошибок остаются в транзакции по завершении задачи. Фиксация или операция отката этих пакетов произойдет вместе со всем пакетом.

Ошибка задачи «Массовая вставка» не вызывает автоматического отката успешно загруженных пакетов, а успешное завершение задачи не означает, что пакеты автоматически фиксируются. Операции фиксации и отката происходят только в результате настройки свойств пакета и рабочего процесса.

Источник и назначение

При указании расположения исходного текстового файла учитывайте следующее.

Сервер должен иметь разрешение на доступ как к файлу, так и к целевой базе данных.

Сервер запускает задачу «Массовая вставка». Таким образом, любой файл форматирования, используемый задачей, должен располагаться на этом сервере.

Оптимизация производительности

Чтобы улучшить производительность, учтите следующее.

Задача «Массовая вставка» не фиксирует в журнале строки, вызвавшие ошибку. Если эти сведения необходимы, воспользуйтесь выходами ошибок компонентов потока данных для обнаружения строк, вызвавших ошибки в файле исключений.

Пользовательские записи журнала, доступные в задаче «Массовая вставка»

В следующей таблице перечислены пользовательские записи в журнале для задачи «Массовая вставка». Дополнительные сведения см. в разделе Ведение журналов в службах Integration Services (SSIS).

Настройка задачи «Массовая вставка»

Чтобы настроить задачу «Массовая вставка», выполните следующее.

Укажите диспетчер соединений OLE DB для подключения к целевой базе данных SQL Server и таблицу или представление, в которые будут вставляться данные. Задача «Массовая вставка» поддерживает только подключения OLE DB к целевой базе данных.

Чтобы получить доступ к исходному файлу, укажите диспетчер соединения с плоскими файлами или диспетчер подключения файлов. Задача «Массовая вставка» использует диспетчер соединений только для местоположения исходного файла. Задача пропускает другие параметры, выбранные в редакторе диспетчера соединений.

Определите формат, используемый задачей «Массовая вставка», либо при помощи файла форматирования, либо указав разделители столбцов и строк в исходных данных. При использовании файла форматирования укажите для доступа к нему диспетчер подключения файлов.

Укажите, какие действия следует произвести над целевой таблицей или представлением при вставке данных. Эти параметры включают в себя проверочное ограничение, разрешение на вставку данных в столбцы удостоверений, сохранение значений NULL, запуск триггеров, блокировку таблицы.

Введите сведения о пакете вставляемых данных, такие как размер пакета, первая и последняя вставляемая строка файла, число ошибок вставки, которые могут произойти, прежде чем задача прекратит вставку строк, а также имена столбцов, которые будут отсортированы.

Если задача «Массовая вставка» использует для доступа к исходному файлу диспетчер соединений с неструктурированными файлами, задача не использует формат, указанный в нем. Вместо этого задача «Массовая вставка» использует либо формат, указанный в файле форматирования, либо значения свойств задачи RowDelimiter и ColumnDelimiter.

Значения свойств можно задавать с помощью конструктора Integration Services или программными средствами.

Дополнительные сведения об установке этих свойств в конструкторе служб Integration Services см. в следующем разделе:

Настройка задачи «Массовая вставка» программными средствами

Дополнительные сведения об установке этих свойств программными средствами см. в следующем разделе.

Связанные задачи

См. также

Редактор задачи «Массовая вставка» (страница «Соединение»)

Страница Соединение диалогового окна Редактор задачи «Массовая вставка» используется для указания источника и места назначения операции массовой вставки и формата для использования.

Параметры

DestinationTable
Введите имя целевой таблицы или представления или выберите таблицу или представление из списка.

Формат
Выберите источник формата для массовой вставки. Это свойство имеет параметры, указанные в следующей таблице.

Расположение файла задается относительно компонента SQL Server Database Engine, указанного в диспетчере соединений для выполнения этой задачи. Доступ к тестовому файлу можно получить с помощью компонента SQL Server Database Engine на локальном жестком диске на сервере или через общий диск или сопоставленный диск относительно SQL Server. Этот файл не имеет доступа к среде выполнения служб SSIS.

Если пользователь осуществляет доступ к исходному файлу с помощью диспетчера соединений с неструктурированным файлом, в задаче «Массовая вставка» не используется формат, указанный в диспетчере соединений с неструктурированными файлами. Вместо этого задача «Массовая вставка» использует либо формат, указанный в файле форматирования, либо значения свойств задачи RowDelimiter и ColumnDelimiter.

Обновить таблицы
Обновите список таблиц и представлений.

Динамические параметры формата

Формат = Использовать файл

FormatFile
Введите путь к файлу форматирования или нажмите кнопку с многоточием (. ) для поиска этого файла.

Формат = Указать

RowDelimiter
Укажите разделитель строк в файле источника. Значением по умолчанию является .

ColumnDelimiter
Укажите разделитель столбцов в файле источника. Значение по умолчанию составляет Табуляция.

Редактор задачи «Массовая вставка» (страница «Общие»)

Страница Общие диалогового окна Редактор задачи «Массовая вставка» используется для наименования и описания задачи «Массовая вставка».

Параметры

имя;
Задайте уникальное имя задачи «Массовая вставка». Это имя используется в качестве метки для значка задачи.

Имена задач в пределах пакета должны быть уникальными.

Описание
Введите описание задачи «Массовая вставка».

Редактор задачи «Массовая вставка» (страница «Параметры»)

Страница Параметры диалогового окна Редактор задачи «Массовая вставка» используется для установки свойств операции массовой вставки. С помощью задачи «Массовая вставка» большой объем данных копируется в таблицу или представление Microsoft SQL Server.

Сведения о работе с массовой вставкой см. в разделах Задача «Массовая вставка» и BULK INSERT(Transact-SQL).

Параметры

CodePage
Задает кодовую страницу данных в файле данных.

DataFileType
Задает значение типа данных, используемых в операции загрузки.

BatchSize
Задает количество строк в одном пакете. Значение по умолчанию равно количеству строк во всем файле данных. Если параметр BatchSize имеет значение ноль, данные загружаются в одном пакете.

LastRow
Задает последнюю строку для копирования.

FirstRow
Задает первую строку, с которой должно начинаться копирование.

Параметры

SortedData
Определяет предложение ORDER BY в инструкции массовой вставки. Указываемое имя столбца должно быть действительным столбцом в целевой таблице. Значение по умолчанию — false. Это означает, что данные не отсортированы с помощью предложения ORDER BY.

MaxErrors
Определяет максимальное количество ошибок, которое может произойти перед отменой операции массовой вставки. Значение «0» указывает на то, что допускается бесконечное количество ошибок.

Каждая строка, которая не может быть импортирована операцией массовой загрузки, считается за одну ошибку.

Источник

Отдельные операции массового копирования

Самый простой способ массового копирования SQL Server — выполнение одной операции в базе данных. По умолчанию операция массового копирования выполняется как изолированная, т. е. не как транзакция и без возможности отката.

Если при возникновении ошибки необходимо частично или полностью отменить массовое копирование, можно использовать управляемую SqlBulkCopy транзакцию или выполнить операцию массового копирования в существующей транзакции. SqlBulkCopy также будет работать с System.Transactions, если это соединение прикреплено (явно или неявно) к транзакции System.Transactions.

Для массового копирования выполните описанную ниже процедуру.

Подключитесь к исходному серверу и получите данные для копирования. Данные также могут поступать из других источников, если их можно извлечь из объекта IDataReader или DataTable.

Подключитесь к целевому серверу (если только не требуется, чтобы объект SqlBulkCopy сам установил соединение).

Создайте объект SqlBulkCopy, задав все необходимые свойства.

Задайте свойство DestinationTableName, чтобы указать целевую таблицу для операции массовой вставки.

Вызовите один из методов WriteToServer.

Кроме того, если это необходимо, можно обновить свойства и вызвать метод WriteToServer.

Пример

Следующее консольное приложение показывает, как загрузить данные с помощью класса SqlBulkCopy. В этом примере объект SqlDataReader используется, чтобы скопировать данные из таблицы Production.Product в базе данных SQL Server AdventureWorks в такую же таблицу в этой же базе данных.

Этот пример не будет выполняться, если вы не создали рабочие таблицы, как описано в статье Пример установки с помощью инструкций копирования. Этот код предназначен только для демонстрации синтаксиса использования SqlBulkCopy. Если исходная и целевая таблицы расположены в одном экземпляре SQL Server, будет проще и быстрее использовать инструкцию Transact-SQL INSERT … SELECT для копирования данных.

Выполнение операции массового копирования при помощи Transact-SQL и класса команды

Следующий пример демонстрирует использование метода ExecuteNonQuery для выполнения инструкции BULK INSERT.

Путь к источнику данных указан относительно сервера. Для успешного выполнения массового копирования у процесса сервера должен быть доступ к этому пути.

Источник

BULK INSERT (Transact-SQL)

Imports a data file into a database table or view in a user-specified format in SQL Server

Transact-SQL Syntax Conventions

Syntax

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Arguments

database_name

Is the database name in which the specified table or view resides. If not specified, database_name is the current database.

schema_name

Is the name of the table or view schema. schema_name is optional if the default schema for the user performing the bulk-import operation is schema of the specified table or view. If schema is not specified and the default schema of the user performing the bulk-import operation is different from the specified table or view, SQL Server returns an error message, and the bulk-import operation is canceled.

table_name

Is the name of the table or view to bulk import data into. Only views in which all columns refer to the same base table can be used. For more information about the restrictions for loading data into views, see INSERT (Transact-SQL).

FROM ‘ data_file ‘

Is the full path of the data file that contains data to import into the specified table or view. BULK INSERT can import data from a disk or Azure Blob storage (including network, floppy disk, hard disk, and so on).

data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name. A UNC name has the form \\Systemname\ShareName\Path\FileName. For example:

Applies to: SQL Server 2017 (14.x) CTP 1.1 and Azure SQL Database. Beginning with SQL Server 2017 (14.x) CTP1.1, the data_file can be in Azure blob storage. In that case, you need to specify data_source_name option. For an example, see Importing data from a file in Azure blob storage.

Azure SQL Database only supports reading from Azure Blob Storage.

BATCHSIZE =batch_size

Specifies the number of rows in a batch. Each batch is copied to the server as one transaction. If this fails, SQL Server commits or rolls back the transaction for every batch. By default, all data in the specified data file is one batch. For information about performance considerations, see «Remarks,» later in this article.

CHECK_CONSTRAINTS

Specifies that all constraints on the target table or view must be checked during the bulk-import operation. Without the CHECK_CONSTRAINTS option, any CHECK and FOREIGN KEY constraints are ignored, and after the operation, the constraint on the table is marked as not-trusted.

UNIQUE, and PRIMARY KEY constraints are always enforced. When importing into a character column that is defined with a NOT NULL constraint, BULK INSERT inserts a blank string when there is no value in the text file.

At some point, you must examine the constraints on the whole table. If the table was non-empty before the bulk-import operation, the cost of revalidating the constraint may exceed the cost of applying CHECK constraints to the incremental data.

A situation in which you might want constraints disabled (the default behavior) is if the input data contains rows that violate constraints. With CHECK constraints disabled, you can import the data and then use Transact-SQL statements to remove the invalid data.

The MAXERRORS option does not apply to constraint checking.

CODEPAGE = < ‘ACP‘ | ‘OEM‘ | ‘RAW‘ | ‘code_page‘ >

Specifies the code page of the data in the data file. CODEPAGE is relevant only if the data contains char, varchar, or text columns with character values greater than 127 or less than 32. For an example, see Specifying a code page.

CODEPAGE is not a supported option on Linux for SQL Server 2017 (14.x). For SQL Server 2019 (15.x), only the ‘RAW’ option is allowed for CODEPAGE.

Microsoft recommends that you specify a collation name for each column in a format file.

** Important ** Versions prior to SQL Server 2016 (13.x) do not support code page 65001 (UTF-8 encoding).ВВ

DATAFILETYPE = < ‘char’ | ‘native’ | ‘widechar’ | ‘widenative’ >

Specifies that BULK INSERT performs the import operation using the specified data-file type value.

DATA_SOURCE = ‘ data_source_name ‘

Applies to: SQL Server 2017 (14.x) CTP 1.1 and Azure SQL Database. Is a named external data source pointing to the Azure Blob storage location of the file that will be imported. The external data source must be created using the TYPE = BLOB_STORAGE option added in SQL Server 2017 (14.x) CTP 1.1. For more information, see CREATE EXTERNAL DATA SOURCE. For an example, see Importing data from a file in Azure blob storage.

ERRORFILE =’error_file_path‘

Specifies the file used to collect rows that have formatting errors and cannot be converted to an OLE DB rowset. These rows are copied into this error file from the data file «as is.»

ERRORFILE_DATA_SOURCE

‘errorfile_data_source_name’ Applies to: SQL Server 2017 (14.x) CTP 1.1. Is a named external data source pointing to the Azure Blob storage location of the error file that will contain errors found during the import. The external data source must be created using the TYPE = BLOB_STORAGE option added in SQL Server 2017 (14.x) CTP 1.1. For more information, see CREATE EXTERNAL DATA SOURCE.

FIRSTROW = first_row

Specifies the number of the first row to load. The default is the first row in the specified data file. FIRSTROW is 1-based.

The FIRSTROW attribute is not intended to skip column headers. Skipping headers is not supported by the BULK INSERT statement. When skipping rows, the SQL Server Database Engine looks only at the field terminators, and does not validate the data in the fields of skipped rows.

FIRE_TRIGGERS

Specifies that any insert triggers defined on the destination table execute during the bulk-import operation. If triggers are defined for INSERT operations on the target table, they are fired for every completed batch.

If FIRE_TRIGGERS is not specified, no insert triggers execute.

FORMATFILE_DATA_SOURCE = ‘data_source_name’

Applies to: SQL Server 2017 (14.x) 1.1. Is a named external data source pointing to the Azure Blob storage location of the format file that will define the schema of imported data. The external data source must be created using the TYPE = BLOB_STORAGE option added in SQL Server 2017 (14.x) CTP 1.1. For more information, see CREATE EXTERNAL DATA SOURCE.

KEEPIDENTITY

Specifies that identity value or values in the imported data file are to be used for the identity column. If KEEPIDENTITY is not specified, the identity values for this column are verified but not imported and SQL Server automatically assigns unique values based on the seed and increment values specified during table creation. If the data file does not contain values for the identity column in the table or view, use a format file to specify that the identity column in the table or view is to be skipped when importing data; SQL Server automatically assigns unique values for the column. For more information, see DBCC CHECKIDENT (Transact-SQL).

For more information, see about keeping identify values see Keep Identity Values When Bulk Importing Data (SQL Server).

KEEPNULLS

Specifies that empty columns should retain a null value during the bulk-import operation, instead of having any default values for the columns inserted. For more information, see Keep Nulls or Use Default Values During Bulk Import (SQL Server).

KILOBYTES_PER_BATCH = kilobytes_per_batch

Specifies the approximate number of kilobytes (KB) of data per batch as kilobytes_per_batch. By default, KILOBYTES_PER_BATCH is unknown. For information about performance considerations, see «Remarks,» later in this article.

LASTROW = last_row

Specifies the number of the last row to load. The default is 0, which indicates the last row in the specified data file.

MAXERRORS = max_errors

Specifies the maximum number of syntax errors allowed in the data before the bulk-import operation is canceled. Each row that cannot be imported by the bulk-import operation is ignored and counted as one error. If max_errors is not specified, the default is 10.

The MAX_ERRORS option does not apply to constraint checks or to converting money and bigint data types.

ORDER ( < column [ ASC | DESC ] > [ ,. n ] )

Specifies how the data in the data file is sorted. Bulk import performance is improved if the data being imported is sorted according to the clustered index on the table, if any. If the data file is sorted in a different order, that is other than the order of a clustered index key or if there is no clustered index on the table, the ORDER clause is ignored. The column names supplied must be valid column names in the destination table. By default, the bulk insert operation assumes the data file is unordered. For optimized bulk import, SQL Server also validates that the imported data is sorted.

n Is a placeholder that indicates that multiple columns can be specified.

ROWS_PER_BATCH = rows_per_batch

Indicates the approximate number of rows of data in the data file.

By default, all the data in the data file is sent to the server as a single transaction, and the number of rows in the batch is unknown to the query optimizer. If you specify ROWS_PER_BATCH (with a value > 0) the server uses this value to optimize the bulk-import operation. The value specified for ROWS_PER_BATCH should approximately the same as the actual number of rows. For information about performance considerations, see «Remarks,» later in this article.

TABLOCK

Specifies that a table-level lock is acquired for the duration of the bulk-import operation. A table can be loaded concurrently by multiple clients if the table has no indexes and TABLOCK is specified. By default, locking behavior is determined by the table option table lock on bulk load. Holding a lock for the duration of the bulk-import operation reduces lock contention on the table, in some cases can significantly improve performance. For information about performance considerations, see «Remarks,» later in this article.

For columnstore index. the locking behavior is different because it is internally divided into multiple rowsets. Each thread loads data exclusively into each rowset by taking an X lock on the rowset allowing parallel data load with concurrent data load sessions. The use of TABLOCK option will cause thread to take an X lock on the table (unlike BU lock for traditional rowsets) which will prevent other concurrent threads to load data concurrently.

Input file format options

FORMAT = ‘CSV’

Applies to: SQL Server 2017 (14.x) CTP 1.1. Specifies a comma-separated values file compliant to the RFC 4180 standard.

FIELDQUOTE = ‘field_quote’

Applies to: SQL Server 2017 (14.x) CTP 1.1. Specifies a character that will be used as the quote character in the CSV file. If not specified, the quote character («) will be used as the quote character as defined in the RFC 4180 standard.

FORMATFILE = ‘format_file_path‘

Specifies the full path of a format file. A format file describes the data file that contains stored responses created by using the bcp utility on the same table or view. The format file should be used if:

Applies to: SQL Server 2017 (14.x) CTP 1.1 and Azure SQL Database. Beginning with SQL Server 2017 (14.x) CTP 1.1, the format_file_path can be in Azure blob storage.

FIELDTERMINATOR =’field_terminator‘

Specifies the field terminator to be used for char and widechar data files. The default field terminator is \t (tab character). For more information, see Specify Field and Row Terminators (SQL Server).

ROWTERMINATOR =’row_terminator‘

Specifies the row terminator to be used for char and widechar data files. The default row terminator is \r\n (newline character). For more information, see Specify Field and Row Terminators (SQL Server).

Compatibility

BULK INSERT enforces strict data validation and data checks of data read from a file that could cause existing scripts to fail when they are executed on invalid data. For example, BULK INSERT verifies that:

Data Types

String-to-Decimal Data Type Conversions

The string-to-decimal data type conversions used in BULK INSERT follow the same rules as the Transact-SQL CONVERT function, which rejects strings representing numeric values that use scientific notation. Therefore, BULK INSERT treats such strings as invalid values and reports conversion errors.

To work around this behavior, use a format file to bulk import scientific notation float data into a decimal column. In the format file, explicitly describe the column as real or float data. For more information about these data types, see float and real (Transact-SQL).

Format files represent real data as the SQLFLT4 data type and float data as the SQLFLT8 data type. For information about non-XML format files, see Specify File Storage Type by Using bcp (SQL Server).

Example of Importing a Numeric Value that Uses Scientific Notation

This example uses the following table in the bulktest database:

The user wants to bulk import data into the t_float table. The data file, C:\t_float-c.dat, contains scientific notation float data; for example:

When copying this sample, be aware of different text editors and encodings that save tabs characters (\t) as spaces. A tab character is expected later in this sample.

The following format file uses the SQLFLT8 data type to map the second data field to the second column:

To use this format file (using the file name C:\t_floatformat-c-xml.xml ) to import the test data into the test table, issue the following Transact-SQL statement:

Azure SQL Database only supports reading from Azure Blob Storage.

Data Types for Bulk Exporting or Importing SQLXML Documents

To bulk export or import SQLXML data, use one of the following data types in your format file:

General Remarks

For information about preparing data for bulk import, see Prepare Data for Bulk Export or Import (SQL Server).

The BULK INSERT statement can be executed within a user-defined transaction to import data into a table or view. Optionally, to use multiple matches for bulk importing data, a transaction can specify the BATCHSIZE clause in the BULK INSERT statement. If a multiple-batch transaction is rolled back, every batch that the transaction has sent to SQL Server is rolled back.

Interoperability

Importing Data from a CSV file

Beginning with SQL Server 2017 (14.x) CTP 1.1, BULK INSERT supports the CSV format, as does Azure SQL Database. Before SQL Server 2017 (14.x) CTP 1.1, 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. For information about the requirements for importing data from a CSV data file, see Prepare Data for Bulk Export or Import (SQL Server).

Logging Behavior

For information about when row-insert operations that are performed by bulk import into SQL Server are logged in the transaction log, see Prerequisites for Minimal Logging in Bulk Import. Minimal logging is not supported in Azure SQL Database.

Restrictions

When using a format file with BULK INSERT, you can specify up to 1024 fields only. This is same as the maximum number of columns allowed in a table. If you use a format file with BULK INSERT with a data file that contains more than 1024 fields, BULK INSERT generates the 4822 error. The bcp utility does not have this limitation, so for data files that contain more than 1024 fields, use BULK INSERT without a format file or use the bcp command.

Performance Considerations

If the number of pages to be flushed in a single batch exceeds an internal threshold, a full scan of the buffer pool might occur to identify which pages to flush when the batch commits. This full scan can hurt bulk-import performance. A likely case of exceeding the internal threshold occurs when a large buffer pool is combined with a slow I/O subsystem. To avoid buffer overflows on large machines, either do not use the TABLOCK hint (which will remove the bulk optimizations) or use a smaller batch size (which preserves the bulk optimizations).

Because computers vary, we recommend that you test various batch sizes with your data load to find out what works best for you.

With Azure SQL Database, consider temporarily increasing the performance level of the database or instance prior to the import if you are importing a large volume of data.

Security

Security Account Delegation (Impersonation)

If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data. In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.

When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.

For more information about this and other security considerations for using BULK INSERT, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK. ) (SQL Server).

When importing from Azure Blob storage and the data is not public (anonymous access), create a DATABASE SCOPED CREDENTIAL based on a SAS key encrypted with a MASTER KEY, and then create an external database source for use in your BULK INSERT command. For an example, see Importing data from a file in Azure blob storage.

Permissions

Requires INSERT and ADMINISTER BULK OPERATIONS permissions. In Azure SQL Database, INSERT and ADMINISTER DATABASE BULK OPERATIONS permissions are required. ADMINISTER BULK OPERATIONS permissions or the bulkadmin role is not supported for SQL Server on Linux. Only the sysadmin can perform bulk inserts for SQL Server on Linux.

Additionally, ALTER TABLE permission is required if one or more of the following is true:

Constraints exist and the CHECK_CONSTRAINTS option is not specified.

Disabling constraints is the default behavior. To check constraints explicitly, use the CHECK_CONSTRAINTS option.

Triggers exist and the FIRE_TRIGGER option is not specified.

By default, triggers are not fired. To fire triggers explicitly, use the FIRE_TRIGGER option.

You use the KEEPIDENTITY option to import identity value from data file.

Examples

A. Using pipes to import data from a file

The following example imports order detail information into the AdventureWorks2012.Sales.SalesOrderDetail table from the specified data file by using a pipe ( | ) as the field terminator and |\n as the row terminator.

Azure SQL Database only supports reading from Azure Blob Storage.

B. Using the FIRE_TRIGGERS argument

The following example specifies the FIRE_TRIGGERS argument.

Azure SQL Database only supports reading from Azure Blob Storage.

C. Using line feed as a row terminator

The following example imports a file that uses the line feed as a row terminator such as a UNIX output:

Due to how Microsoft Windows treats text files (\n automatically gets replaced with \r\n).

Azure SQL Database only supports reading from Azure Blob Storage.

D. Specifying a code page

The following example shows how to specify a code page.

Azure SQL Database only supports reading from Azure Blob Storage.

E. Importing data from a CSV file

The following example shows how to specify a CSV file, skipping the header (first row), using ; as field terminator and 0x0a as line terminator:

Azure SQL Database only supports reading from Azure Blob Storage.

F. Importing data from a file in Azure blob storage

The following example shows how to load data from a csv file in an Azure Blob storage location on which you have created a SAS key. The Azure Blob storage location is configured as an external data source. This requires a database scoped credential using a shared access signature that is encrypted using a master key in the user database.

Azure SQL only supports reading from Azure Blob Storage.

G. Importing data from a file in Azure blob storage and specifying an error file

The following example shows how to load data from a csv file in an Azure blob storage location, which has been configured as an external data source and also specifying an error file. This requires a database scoped credential using a shared access signature. Note that if running on Azure SQL Database, ERRORFILE option should be accompanied by ERRORFILE_DATA_SOURCE otherwise the import might fail with permissions error. The file specified in ERRORFILE should not exist in the container.

For complete BULK INSERT examples including configuring the credential and external data source, see Examples of Bulk Access to Data in Azure Blob Storage.

Additional Examples

Other BULK INSERT examples are provided in the following articles:

Источник