Поделиться через

ОБЪЕМНАЯ ВСТАВКА (Transact-SQL)

Применимо к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureХранилище в Microsoft Fabric

Импортирует файл данных в таблицу базы данных или представление в указанном пользователем формате в SQL Server.

Соглашения о синтаксисе Transact-SQL

Синтаксис

BULK INSERT
   { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] DATA_SOURCE = 'data_source_name' ]

   -- text formatting options
   [ [ , ] CODEPAGE = { 'RAW' | 'code_page' | 'ACP' | 'OEM' } ]
   [ [ , ] DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' } ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] FORMAT = 'CSV' ]
   [ [ , ] FIELDQUOTE = 'quote_characters' ]

   [ [ , ] FIRSTROW = first_row ]
   [ [ , ] LASTROW = last_row ]

   -- input file format options
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] FORMATFILE_DATA_SOURCE = 'data_source_name' ]

   -- error handling options
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ERRORFILE = 'file_name' ]
   [ [ , ] ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name' ]

   -- database options
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] TABLOCK ]

   -- source options
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ , ...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch
   [ [ , ] BATCHSIZE = batch_size ]

    ) ]

Аргументы

Оператор BULK INSERT имеет различные аргументы и параметры на разных платформах. Различия приведены в следующей таблице:

Особенность SQL Server База данных SQL Azure и Управляемый экземпляр SQL Azure Хранилище данных Fabric
Источник данных Локальный путь, сетевой путь (UNC) или служба хранилища Azure Служба хранилища Azure Служба хранилища Azure
Проверка подлинности источника Проверка подлинности Windows, SAS Идентификатор Microsoft Entra, маркер SAS, управляемое удостоверение Идентификатор Microsoft Entra
Неподдерживаемые параметры * подстановочные знаки в пути * подстановочные знаки в пути DATA_SOURCE, , FORMATFILE_DATA_SOURCEERRORFILEERRORFILE_DATA_SOURCE
Включенные параметры, но без эффекта KEEPIDENTITY, FIRE_TRIGGERS, , TABLOCKROWS_PER_BATCHKILOBYTES_PER_BATCHCHECK_CONSTRAINTSORDERи BATCHSIZE не применимы. Они не вызывают синтаксическую ошибку, но они не имеют никакого эффекта

database_name

Имя базы данных, где находится указанная таблица или представление. Если не указано, в качестве database_name используется текущая база данных.

schema_name

Указывает имя схемы таблицы или представления. Указание аргумента schema_name необязательно, если схемой по умолчанию для пользователя, выполняющего операцию массового импорта, является схема указанной таблицы или представления. Если аргумент schema не указан и схема по умолчанию для пользователя, выполняющего операцию массового импорта, отличается от указанной таблицы или представления, SQL Server возвращает сообщение об ошибке, а операция массового импорта не выполняется.

table_name

Указывает имя таблицы или представления, куда производится массовый импорт данных. Могут указываться только те представления, в которых все столбцы относятся к одной и той же базовой таблице. Дополнительные сведения об ограничениях для загрузки данных в представления см. в разделе INSERT.

FROM "data_file"

Указывает полный путь файла данных, который содержит импортируемые в указанную таблицу данные или представление. BULK INSERT может импортировать данные из диска или хранилища BLOB-объектов Azure (включая сеть, диск floppy, жесткий диск и т. д.).

BULK INSERT bing_covid_19_data
FROM 'C:\\bing_covid-19_data\public\curated\covid-19\latest\bing_covid-19_data.csv';

data_file должен указать допустимый путь от сервера, на котором выполняется SQL Server. Если аргумент data_file является удаленным файлом, указывайте имя в формате UNC. Имя UNC имеет форму \\SystemName\ShareName\Path\FileName. Например:

BULK INSERT bing_covid_19_data
FROM '\\ShareX\bing_covid-19_data\public\curated\covid-19\latest\bing_covid-19_data.csv';

База данных SQL Azure и хранилище Fabric поддерживают только чтение из хранилища BLOB-объектов Azure.

Начиная с SQL Server 2017 (14.x), аргумент data_file может находиться в Хранилище BLOB-объектов Azure. В этом случае также необходимо указать параметр data_source_name. Пример см. в разделе об импорте данных из файла в Хранилище BLOB-объектов Azure.

Хранилище Fabric поддерживает два разных стиля пути для указания исходного пути:

  • https://<storage account>.blob.core.windows.net/<container name>/<path to file>
  • abfss://<container name>@<storage account>.dfs.core.windows.net/<path to file>

Хранилище Fabric поддерживает * подстановочные знаки, которые могут соответствовать любому символу в URI и позволяют определить шаблон URI для импортируемых файлов. Например:

BULK INSERT bing_covid_19_data
FROM 'https://<data-lake>.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/*.csv';

Примечание.

Замените <data-lake>.blob.core.windows.net соответствующим URL-адресом.

ATCHSIZE = batch_size

Указывает число строк в одном пакете. Каждый пакет копируется на сервер за одну транзакцию. Если это не удается, SQL Server фиксирует или откатывает транзакцию для каждого пакета. По умолчанию, все данные, содержащиеся в файле, передаются одним пакетом. Сведения о вопросах производительности см. в этом разделе далее в этой статье.

ПРОВЕРКА_ОГРАНИЧЕНИЙ

Указывает, что при выполнении операции массового импорта будет выполняться проверка всех ограничений целевой таблицы или представления. CHECK_CONSTRAINTS Без параметра все CHECK и FOREIGN KEY ограничения игнорируются, а после операции ограничение таблицы помечается как ненадежный.

UNIQUE и PRIMARY KEY ограничения всегда применяются. При импорте в символьный столбец, определенный с NOT NULL ограничением, BULK INSERT вставляет пустую строку, если в текстовом файле нет значения.

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

Отключение проверки ограничений (настройка по умолчанию) может потребоваться в тех ситуациях, когда входные данные содержат строки, нарушающие эти ограничения. С CHECK отключенными ограничениями можно импортировать данные, а затем использовать инструкции Transact-SQL для удаления недопустимых данных.

Примечание.

Параметр MAXERRORS не применяется к проверке ограничений.

CODEPAGE = { "ACP" | 'OEM' | 'RAW' | 'code_page' }

Указывает кодовую страницу данных в файле данных. CODEPAGE имеет значение, только если данные содержат char, varchar или текстовые столбцы со значениями символов, превышающими 127 или меньше 32. Пример см. в статье об указании кодовой страницы.

BULK INSERT bing_covid_19_data
FROM 'https://<data-lake>.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
WITH (CODEPAGE = '65001', FIRSTROW = 2);

Примечание.

Замените <data-lake>.blob.core.windows.net соответствующим URL-адресом.

CODEPAGE не поддерживается в Linux для SQL Server 2017 (14.x). Для SQL Server 2019 (15.x) разрешен CODEPAGEтолько 'RAW' параметр.

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

Значение CODEPAGE Описание
ACP Столбцы с типами данных char, varchar или text преобразуются из кодовой страницы ANSI/Microsoft Windows (ISO 1252) в кодовую страницу SQL Server.
OEM (по умолчанию) Столбцы типа данных char, varchar или text преобразуются с системной OEM кодовой страницы на кодовую страницу SQL Server.
RAW Преобразование из одной кодовой страницы в другую не выполняется. RAW — самый быстрый вариант.
code_page Номер кодовой страницы, например 850.

Версии до SQL Server 2016 (13.x) не поддерживают кодовую страницу 65001 (кодировка UTF-8).

DATAFILETYPE = { "char" | "native" | 'widechar' | 'widenative' }

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

BULK INSERT bing_covid_19_data
FROM 'https://<data-lake>.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
WITH (DATAFILETYPE = 'char', FIRSTROW = 2);

Примечание.

Замените <data-lake>.blob.core.windows.net соответствующим URL-адресом.

Значение DATAFILETYPE Все данные, представленные в
char (по умолчанию) В символьном формате.

Дополнительные сведения см. в разделе "Использование формата символов для импорта или экспорта данных (SQL Server)".
native В собственных типах базы данных. Создайте собственный файл данных путем массового импорта данных из SQL Server с помощью служебной программы bcp .

Значение собственного типа обеспечивает более высокую производительность по сравнению со значением типа char. Собственный формат данных рекомендуется использовать во время массовой передачи данных между несколькими экземплярами SQL Server при помощи файла данных, не содержащего символы в расширенной или в двухбайтовой кодировке (DBCS).

Дополнительные сведения см. в статье "Использование собственного формата для импорта или экспорта данных (SQL Server)".
widechar Знаки Юникода.

Дополнительные сведения см. в разделе "Использование формата символов Юникода" для импорта или экспорта данных (SQL Server).
widenative В собственных типах базы данных, за исключением столбцов типа char, varchar и text, в которых данные хранятся в Юникоде. widenative Создайте файл данных путем массового импорта данных из SQL Server с помощью служебной программы bcp.

Это widenative значение обеспечивает более высокую производительность widechar. Если файл данных содержит расширенные символы ANSI, укажите widenative.

Дополнительные сведения см. в разделе Использование собственного формата Юникод для импорта и экспорта данных (SQL Server).

DATA_SOURCE = "data_source_name"

Применимо к: SQL Server 2017 (14.x) и более поздних версий и Базы данных SQL Azure.

Указывает именованный внешний источник данных, указывающий расположение импортируемого файла в Хранилище BLOB-объектов Azure. Внешний источник данных должен быть создан с помощью параметра TYPE = BLOB_STORAGE, который доступен в SQL Server 2017 (14.x). Дополнительные сведения см. в разделе CREATE EXTERNAL DATA SOURCE. Пример см. в разделе об импорте данных из файла в Хранилище BLOB-объектов Azure.

Примечание.

Замените <data-lake>.blob.core.windows.net соответствующим URL-адресом.

CREATE EXTERNAL DATA SOURCE pandemicdatalake
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://<data-lake>.blob.core.windows.net/public/'
);
GO

BULK INSERT bing_covid_19_data
FROM 'curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
WITH (DATA_SOURCE = '<data-lake>', FIRSTROW = 2, LASTROW = 100, FIELDTERMINATOR = ',');

ERRORFILE = "error_file_path"

Указывает файл, используемый для сбора строк, содержащих ошибки форматирования, которые не могут быть преобразованы в набор строк OLE DB. Эти строки без изменений копируются из файла данных в файл ошибок.

Файл ошибок создается на стадии выполнения команды. Если он уже существует, возникает ошибка. Кроме того, создается файл управления с расширением .ERROR.txt , который ссылается на каждую строку в файле ошибки и предоставляет диагностику ошибок. После исправления ошибок эти данные могут быть повторно загружены.

Начиная с SQL Server 2017 (14.x), аргумент error_file_path может находиться в Хранилище BLOB-объектов Azure.

ERRORFILE_DATA_SOURCE = "errorfile_data_source_name"

Область применения: SQL Server 2017 (14.x) и более поздних версий.

Указывает именованный внешний источник данных, указывающий на расположение хранилища BLOB-объектов Azure файла ошибок для отслеживания ошибок, обнаруженных во время импорта. Внешний источник данных должен быть создан с помощью параметра TYPE = BLOB_STORAGE, который доступен в SQL Server 2017 (14.x). Дополнительные сведения см. в разделе CREATE EXTERNAL DATA SOURCE.

FIRSTROW = first_row

Указывает номер первой строки для загрузки. Значение по умолчанию — первая строка указанного файла данных. FIRSTROW — 1 на основе.

BULK INSERT bing_covid_19_data
FROM 'https://<data-lake>.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
WITH (FIRSTROW = 2);

Примечание.

Замените <data-lake>.blob.core.windows.net соответствующим URL-адресом.

Атрибут FIRSTROW не предназначен для пропуска заголовков столбцов. Инструкция BULK INSERT не поддерживает пропуск заголовков. В случае пропуска строк ядро СУБД SQL Server выполняет поиск только в признаках конца поля и не проверяет данные в полях пропущенных строк.

СОБЫТИЯ_ПОЖАРА

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

Если FIRE_TRIGGERS не указано, триггеры вставки не выполняются.

FORMATFILE_DATA_SOURCE = "data_source_name"

Область применения: SQL Server 2017 (14.x) и более поздних версий.

Указывает именованный внешний источник данных, указывающий на расположение хранилища BLOB-объектов Azure файла форматирования, чтобы определить схему импортированных данных. Внешний источник данных должен быть создан с помощью параметра TYPE = BLOB_STORAGE, который доступен в SQL Server 2017 (14.x). Дополнительные сведения см. в разделе CREATE EXTERNAL DATA SOURCE.

KEEPIDENTITY

Указывает, что значение или значения идентификаторов в файле импортированных данных будут использоваться для столбца идентификаторов. Если KEEPIDENTITY это не указано, значения удостоверений для этого столбца проверяются, но не импортируются, а SQL Server автоматически назначает уникальные значения на основе значений начального значения и добавок, указанных во время создания таблицы. Если файл данных не содержит значений для столбца идентификаторов в таблице или представлении , укажите в файле форматирования, что столбец идентификаторов в таблице или представлении при импорте данных следует пропустить. В этом случае SQL Server автоматически назначит уникальные значения для этого столбца. Дополнительные сведения см. в статье DBCC CHECKIDENT.

Дополнительные сведения о сохранении значений идентификации см. в статье "Сохранение значений удостоверений при массовом импорте данных (SQL Server)".

KEEPNULLS

Указывает, что пустым столбцам при массовом импорте должны присваиваться значения NULL, а не значения по умолчанию, назначенные для этих столбцов. Дополнительные сведения см. в разделе "Сохранение значений NULL" или значений по умолчанию во время массового импорта (SQL Server).

KILOBYTES_PER_BATCH = kilobytes_per_batch

Определяет приблизительное число килобайт данных в пакете как kilobytes_per_batch. По умолчанию KILOBYTES_PER_BATCH неизвестно. Сведения о вопросах производительности см. в этом разделе далее в этой статье.

LASTROW = last_row

Указывает номер последней строки для загрузки. Значение по умолчанию 0, что обозначает последнюю строку в указанном файле данных.

MAXERRORS = max_errors

Указывает максимальное число синтаксических ошибок, допустимых для файла данных, прежде чем операция массового импорта будет отменена. Каждая строка, импорт которой нельзя выполнить при массовом импорте, пропускается и считается как одна ошибка. Если аргумент max_errors не указан, значение по умолчанию равно 10.

Параметр MAX_ERRORS не применяется к проверкам ограничения или преобразованию типов данных money и bigint.

ПОРЯДОК ( { столбец [ ASC | DESC ] } [ ,... n ] )

Указывает, каким образом отсортированы данные в файле. Производительность массового импорта увеличивается, если импортируемые данные упорядочены согласно кластеризованному индексу таблицы (при наличии). Если файл данных отсортирован в порядке, отличном от порядка кластеризованного ключа индекса, или если в таблице нет кластеризованного индекса, предложение ORDER игнорируется. В целевой таблице должны быть указаны имена столбцов. По умолчанию, операция массовой вставки считает, что файл данных не отсортирован. Для оптимизированного массового импорта SQL Server также проверяет, отсортированы ли импортированные данные.

n — это заполнитель, обозначающий возможность указания нескольких столбцов.

ROWS_PER_BATCH = rows_per_batch

Указывает приблизительное число строк в файле данных.

По умолчанию все данные в файле отправляются на сервер за одну транзакцию, а число строк в пакете оптимизатору запросов неизвестно. При указании ROWS_PER_BATCH (со значением > 0) сервер использует это значение для оптимизации операции массового импорта. Указанное значение ROWS_PER_BATCH должно быть примерно таким же, как фактическое количество строк. Сведения о вопросах производительности см. в этом разделе далее в этой статье.

TABLOCK

Указывает необходимость запроса блокировки уровня таблицы на время выполнения массового импорта. Таблица может загружаться одновременно несколькими клиентами, если таблица не имеет индексов и TABLOCK не указана. По умолчанию работа блокировки определяется параметром таблицы table lock on bulk load. Блокировка на время выполнения массового импорта значительно повышает производительность, позволяя снизить состязание блокировок таблицы. Сведения о вопросах производительности см. в этом разделе далее в этой статье.

Для индекса columnstore блокировка будет действовать иначе из-за внутреннего разделения на множество наборов строк. Каждый поток загружает данные исключительно в каждый набор строк, принимая монопольную блокировку (X) в наборе строк, позволяя параллельно загружать данные с одновременными сеансами загрузки данных. TABLOCK Использование параметра приводит к тому, что поток принимает монопольную блокировку таблицы (в отличие от блокировки массового обновления (BU) для традиционных наборов строк, что предотвращает одновременную загрузку данных другими параллельными потоками.

Параметры формата входного файла

FORMAT = "CSV"

Область применения: SQL Server 2017 (14.x) и более поздних версий.

указывает файл данных с разделителями-запятыми, соответствующий стандарту RFC 4180.

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.csv'
WITH (FORMAT = 'CSV');

FIELDQUOTE = "field_quote"

Область применения: SQL Server 2017 (14.x) и более поздних версий.

Задает символ, используемый в качестве символа кавычки в CSV-файле. Если не указано, символ кавычки (") используется в качестве символа кавычки, как определено в стандарте RFC 4180 .

FORMATFILE = 'путь_к_файлу_форматирования'

Указывает полный путь к файлу форматирования. Этот файл форматирования содержит описание файла данных — сведения, полученные путем применения программы bcp к той же таблице или представлению. И предназначен для случаев, когда:

  • файл данных содержит больше или меньше столбцов, чем таблица или представление;
  • столбцы расположены в другом порядке;
  • отличаются разделители столбцов;
  • имеются какие-либо другие изменения в формате данных. Файлы форматирования обычно создаются с помощью программы bcp и затем при необходимости изменяются в текстовом редакторе. Дополнительные сведения см. в статье bcp Utility and Create a format file with bcp (SQL Server).

Начиная с SQL Server 2017 (14.x) и в Базе данных SQL Azure, format_file_path может находиться в Хранилище BLOB-объектов Azure.

FIELDTERMINATOR = "field_terminator"

Указывает признак конца поля, используемый для файлов данных типа char и widechar. По умолчанию, признаком конца поля является символ \t (символ табуляции). Дополнительные сведения см. в разделе "Указание терминаторов полей и строк" (SQL Server).

BULK INSERT bing_covid_19_data
FROM 'https://<data-lake>.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
WITH (FIELDTERMINATOR = ',', FIRSTROW = 2);

Примечание.

Замените <data-lake>.blob.core.windows.net соответствующим URL-адресом.

ROWTERMINATOR = "row_terminator"

Указывает признак конца строки, используемый для файлов данных типа char и widechar. По умолчанию признаком конца строки является символ \r\n (символ новой строки). Дополнительные сведения см. в разделе "Указание терминаторов полей и строк" (SQL Server).

Совместимость

BULK INSERT применяет строгую проверку данных и проверки данных, считываемые из файла, которые могут привести к сбою существующих скриптов при выполнении недопустимых данных. Например, проверяет, BULK INSERT что:

  • собственные представления типов данных float или real являются допустимыми;
  • Данные в Юникоде имеют четную длину.

Типы данных

Преобразования символьного типа данных в десятичный

Преобразования типов данных строки в десятичные значения, используемые в BULK INSERT следующих правилах, как функция Transact-SQL CONVERT , которая отклоняет строки, представляющие числовые значения, использующие научное нотацию. Таким образом, BULK INSERT такие строки рассматриваются как недопустимые значения и сообщения об ошибках преобразования.

Чтобы решить эту проблему, применяется файл форматирования, позволяющий выполнить массовый импорт данных типа float в экспоненциальном представлении в десятичный столбец. В файле форматирования необходимо явно описать столбец с типом данных real или float. Дополнительные сведения об этих типах данных см. в статье float и real.

Файлы форматирования представляют данные real в виде типа данных SQLFLT4, а данные float — в виде типа данных SQLFLT8. Сведения о файлах форматирования, отличных от XML, см. в разделе "Указание типа хранилища файлов" с помощью bcp (SQL Server).

Пример импорта числового значения в экспоненциальном представлении

Этот пример использует следующую таблицу в базе данных bulktest:

CREATE TABLE dbo.t_float
(
    c1 FLOAT,
    c2 DECIMAL (5, 4)
);

Пользователю необходимо выполнить массовый импорт данных в таблицу t_float. Файл данных содержит C:\t_float-c.datданные с плавающей запятой научной нотации; например:

8.0000000000000002E-2 8.0000000000000002E-2

При копировании этого образца следует учитывать, что некоторые текстовые редакторы и кодировки сохраняют символы табуляции (\t) в виде пробелов. Символ табуляции ожидается в этом примере позже.

BULK INSERT Однако не удается импортировать эти данные непосредственно в t_float, так как второй столбец c2использует десятичный тип данных. Поэтому необходим файл форматирования. В нем данные типа float в экспоненциальном представлении должны быть сопоставлены десятичному формату столбца c2.

Следующий файл формата использует тип данных SQLFLT8 для сопоставления второго поля данных со вторым столбцом:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" />
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" />
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="c1" xsi:type="SQLFLT8" />
    <COLUMN SOURCE="2" NAME="c2" xsi:type="SQLFLT8" />
  </ROW>
</BCPFORMAT>

Для использования этого файла форматирования (с именем файла C:\t_floatformat-c-xml.xml) при импорте тестовых данных в тестовую таблицу, необходимо выполнить следующую инструкцию Transact-SQL:

BULK INSERT bulktest.dbo.t_float
FROM 'C:\t_float-c.dat'
WITH (FORMATFILE = 'C:\t_floatformat-c-xml.xml');

Внимание

База данных SQL Azure и хранилище Fabric поддерживают только чтение из хранилища BLOB-объектов Azure.

Типы данных для массового экспорта или импорта документов SQLXML

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

Тип данных Действие
SQLCHAR или SQLVARCHAR Данные отправляются в кодовой странице клиента или кодовой странице, определенной параметрами сортировки. Эффект совпадает с указанием DATAFILETYPE = 'char' без указания файла форматирования.
SQLNCHAR или SQLNVARCHAR Данные отправляются в Юникоде. Эффект совпадает с указанием DATAFILETYPE = 'widechar' без указания файла форматирования.
SQLBINARY или SQLVARBIN Данные отправляются без преобразования.

Замечания

Сравнение инструкции, инструкции BULK INSERT и bcp команды см. в разделе "Массовый импорт и экспорт данных" (SQL Server).INSERT ... SELECT * FROM OPENROWSET(BULK...)

Сведения о подготовке данных для массового импорта см. в разделе "Подготовка данных для массового экспорта или импорта".

Инструкцию BULK INSERT можно выполнить в определяемой пользователем транзакции для импорта данных в таблицу или представление. При необходимости для использования нескольких совпадений для массового импорта данных транзакция может указать BATCHSIZE предложение в инструкции BULK INSERT . Если откат транзакции с несколькими пакетами выполняется откат, откат выполняется каждый пакет, отправляемый транзакцией в SQL Server.

Совместимость

Импорт данных из CSV-файла

Начиная с SQL Server 2017 (14.x), BULK INSERT поддерживает формат CSV, как и база данных SQL Azure.

До SQL Server 2017 (14.x) файлы со значениями с разделителями-запятыми (CSV) не поддерживаются в операциях массового импорта SQL Server. Но в некоторых случаях файл CSV может использоваться как файл данных для массового импорта данных в SQL Server. Сведения о требованиях к импорту данных из CSV-файла см. в разделе "Подготовка данных для массового экспорта или импорта".

Поведение журнала

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

Ограничения

При использовании файла BULK INSERTформатирования можно указать только до 1024 полей. Это значение совпадает с максимальным числом столбцов в таблице. При использовании файла форматирования с BULK INSERT файлом данных, содержащим более 1024 полей, BULK INSERT возникает ошибка 4822. Программа bcp не имеет этого ограничения, поэтому для файлов данных, содержащих более 1024 полей, используйте BULK INSERT без файла форматирования или используйте команду bcp .

Замечания, связанные с быстродействием

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

Необходимо протестировать свою рабочую нагрузку с различными размерами пакетов, чтобы определить оптимальный вариант. Помните, что размер пакета имеет последствия частичного отката. Если процесс завершается сбоем и перед повторным использованием BULK INSERT , возможно, потребуется выполнить дополнительную ручную работу, чтобы удалить часть строк, которые были вставлены успешно, до сбоя.

При использовании Базы данных SQL Azure рекомендуется временно увеличить уровень производительности базы данных или экземпляра перед импортом, если вы импортируете большой объем данных.

Безопасность

Делегирование учетных записей безопасности (олицетворение)

Если пользователь использует имя входа SQL Server, используется профиль безопасности учетной записи процесса SQL Server. За пределами ядра СУБД нельзя выполнить проверку подлинности имени входа, проходящего проверку подлинности SQL Server. Поэтому при BULK INSERT запуске команды с помощью проверки подлинности SQL Server подключение к данным выполняется с помощью контекста безопасности учетной записи процесса SQL Server (учетная запись, используемая службой ядра СУБД SQL Server).

Чтобы успешно считывать исходные данные, необходимо предоставить учетную запись, используемую ядром СУБД SQL Server, доступ к исходным данным. В отличие от этого, если пользователь SQL Server входит в систему с помощью проверки подлинности Windows, пользователь может читать только те файлы, к которым можно получить доступ учетной записи пользователя, независимо от профиля безопасности процесса SQL Server.

При выполнении BULK INSERT инструкции с помощью sqlcmd или osql с одного компьютера, вставки данных в SQL Server на втором компьютере и указания data_file на третьем компьютере с помощью UNC-пути может возникнуть ошибка 4861.

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

Дополнительные сведения об этом и других рекомендациях BULK INSERTпо обеспечению безопасности см. в разделе "Использование BULK INSERT" или OPENROWSET(BULK...) для импорта данных в SQL Server.

При импорте из хранилища BLOB-объектов Azure и данных не является общедоступным (анонимным доступом), создайте учетные данные DATABASE SCOPED на основе ключа SAS, зашифрованного с помощью MASTER KEY, а затем создайте внешний источник базы данных для использования в команде BULK INSERT .

Кроме того, создайте УЧЕТНЫе данные DATABASE SCOPED на MANAGED IDENTITY основе авторизации запросов на доступ к данным в учетных записях хранения, не являющихся общедоступными. При использовании MANAGED IDENTITYхранилище Azure должно предоставить разрешения управляемому удостоверению экземпляра, добавив встроенную роль управления доступом на основе ролей Azure (RBAC), которая предоставляет доступ на чтение и запись к управляемому удостоверению для необходимых контейнеров Хранилище BLOB-объектов Azure. Управляемый экземпляр SQL Azure иметь управляемое удостоверение, назначаемое системой, а также может иметь одно или несколько управляемых удостоверений, назначаемых пользователем. Для авторизации запросов можно использовать управляемые удостоверения, назначаемые системой или пользователем. Для авторизации default будет использоваться удостоверение управляемого экземпляра (то есть основное управляемое удостоверение, назначаемое пользователем, или управляемое удостоверение, назначаемое системой, если назначаемое пользователем управляемое удостоверение не указано). Пример см. в разделе об импорте данных из файла в Хранилище BLOB-объектов Azure.

Внимание

Управляемое удостоверение относится к SQL Azure и SQL Server 2025 (17.x) предварительной версии и более поздним версиям.

Разрешения

Следующие разрешения применяются к расположению, в котором данные импортируются массово (целевой объект).

Требуются INSERT и ADMINISTER BULK OPERATIONS разрешения. В Базе данных INSERT SQL Azure и ADMINISTER DATABASE BULK OPERATIONS необходимы разрешения. ADMINISTER BULK OPERATIONS разрешения или роль bulkadmin не поддерживаются для SQL Server в Linux. Операции массовой вставки для SQL Server на Linux может выполнять только sysadmin.

Кроме того, требуется разрешение, ALTER TABLE если одно или несколько следующих условий имеет значение true:

  • Существуют ограничения, и CHECK_CONSTRAINTS параметр не указан.

    Ограничения отключены по умолчанию. Чтобы явным образом проверить ограничения, используйте CHECK_CONSTRAINTS этот параметр.

  • Триггеры существуют, и FIRE_TRIGGER параметр не указан.

    Триггеры не срабатывают по умолчанию. Для явного FIRE_TRIGGER срабатывания триггеров используйте этот параметр.

  • Вы используете KEEPIDENTITY параметр импорта значения удостоверения из файла данных.

Примеры

Примеры кода в этой статье используют базу данных образца AdventureWorks2022 или AdventureWorksDW2022, которую можно скачать с домашней страницы образцов и проектов сообщества Microsoft SQL Server и.

Внимание

База данных SQL Azure и хранилище Fabric поддерживают только чтение из хранилища BLOB-объектов Azure.

А. Применение символов вертикальной черты для импорта данных из файла

В следующем примере выполняется импорт подробных сведений о заказах из указанного файла данных в таблицу AdventureWorks2022.Sales.SalesOrderDetail, используя символ вертикальной черты (|) в качестве признака конца столбца и |\n в качестве признака конца строки.

BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
FROM 'f:\orders\lineitem.tbl'
WITH (FIELDTERMINATOR = ' |', ROWTERMINATOR = ' |\n');

В. Применение аргумента FIRE_TRIGGERS

В следующем примере указывается аргумент FIRE_TRIGGERS.

BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
FROM 'f:\orders\lineitem.tbl'
WITH (FIELDTERMINATOR = ' |', ROWTERMINATOR = ':\n', FIRE_TRIGGERS);

В. Применение символа перевода строки в качестве признака конца строки

В следующем примере производится импорт файла, в котором в качестве признака конца строки используется символ перевода строки, как в файлах UNIX.

DECLARE @bulk_cmd AS VARCHAR (1000);

SET @bulk_cmd = 'BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>''
WITH (ROWTERMINATOR = ''' + CHAR(10) + ''')';

EXECUTE (@bulk_cmd);

Примечание.

В Windows \n автоматически заменяется \r\n.

Д. Указание кодовой страницы

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

BULK INSERT MyTable
FROM 'D:\data.csv'
WITH (CODEPAGE = '65001', DATAFILETYPE = 'char', FIELDTERMINATOR = ',');

Е. Импорт данных из CSV-файла

В следующем примере показано, как указать CSV-файл с пропуском заголовка (первой строки), используя ; в качестве признака конца поля и 0x0a в качестве признака конца строки:

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH (
    FORMAT = 'CSV',
    FIRSTROW = 2,
    FIELDQUOTE = '\',
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = '0x0a'
);

В следующем примере показано, как указать CSV-файл в формате UTF-8 (используя CODEPAGE со значением 65001) с пропуском заголовка (первой строки), используя ; в качестве признака конца поля и 0x0a в качестве признака конца строки:

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH (
    CODEPAGE = '65001',
    FORMAT = 'CSV',
    FIRSTROW = 2,
    FIELDQUOTE = '\',
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = '0x0a'
);

F. Импорт данных из файла в Хранилище BLOB-объектов Azure

В следующем примере показано, как загрузить данные из CSV-файла в Хранилище BLOB-объектов Azure, для которого был создан подписанный URL-адрес (SAS). Расположение хранилища BLOB-объектов Azure настраивается как внешний источник данных, для которого требуются учетные данные с областью базы данных с помощью ключа SAS, зашифрованного с помощью главного ключа в пользовательской базе данных.

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';
-- NOTE: Make sure that you don't have a leading ? in SAS token, and
-- that you have at least read permission on the object that should be loaded srt=o&sp=r, and
-- that expiration period is valid (all dates are in UTC time)

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://****************.blob.core.windows.net/invoices',
    CREDENTIAL = MyAzureBlobStorageCredential
--> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

В следующем примере показано, как использовать BULK INSERT команду для загрузки данных из CSV-файла в расположении хранилища BLOB-объектов Azure с помощью управляемого удостоверения. Расположение хранилища BLOB-объектов Azure настроено как внешний источник данных.

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
    WITH IDENTITY = 'Managed Identity';

-- NOTE: Make sure you have granted Storage Bob Data Contributor RBAC on storage to provides read/write access to the managed identity for the necessary Azure Blob Storage containers.
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://****************.blob.core.windows.net/invoices',
    CREDENTIAL = MyAzureBlobStorageCredential
--> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

Внимание

Управляемое удостоверение относится к SQL Azure и SQL Server 2025 (17.x) предварительной версии и более поздним версиям.

G. Импорт данных из файла в Хранилище BLOB-объектов Azure и определение файла с ошибкой

В следующем примере показано, как загрузить данные из CSV-файла в расположении хранилища BLOB-объектов Azure, настроенном как внешний источник данных, а также указать файл ошибки. Необходимы учетные данные базы данных с помощью подписанного URL-адреса. При запуске в Базе данных ERRORFILE SQL Azure параметр должен сопровождаться ошибкой ERRORFILE_DATA_SOURCE импорта с ошибками разрешений. Файл, указанный в ERRORFILE контейнере, не должен существовать.

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (
    DATA_SOURCE = 'MyAzureInvoices',
    FORMAT = 'CSV',
    ERRORFILE = 'MyErrorFile',
    ERRORFILE_DATA_SOURCE = 'MyAzureInvoices'
);

Полные BULK INSERT примеры, включая настройку учетных данных и внешнего источника данных, см. в примерах массового доступа к данным в Хранилище BLOB-объектов Azure.

Дополнительные примеры

Другие примеры использования BULK INSERT приведены в следующих статьях:

Связанный контент

  • Last updated on