BULK INSERT (Transact-SQL)

  • Статья

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

Импортирует файл данных в таблицу базы данных или представление в указанном пользователем формате в 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
    (
   [ [ , ] BATCHSIZE = batch_size ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ [ , ] DATAFILETYPE =
      { 'char' | 'native' | 'widechar' | 'widenative' } ]
   [ [ , ] DATA_SOURCE = 'data_source_name' ]
   [ [ , ] ERRORFILE = 'file_name' ]
   [ [ , ] ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name' ]
   [ [ , ] FIRSTROW = first_row ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] FORMATFILE_DATA_SOURCE = 'data_source_name' ]
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
   [ [ , ] LASTROW = last_row ]
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] TABLOCK ]

   -- input file format options
   [ [ , ] FORMAT = 'CSV' ]
   [ [ , ] FIELDQUOTE = 'quote_characters']
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
    )]

Примечание.

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

database_name

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

schema_name

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

table_name

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

FROM "data_file"

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

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

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.dat';

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

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

ATCHSIZE = batch_size

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

CHECK_CONSTRAINTS

Указывает, что при выполнении операции массового импорта будет выполняться проверка всех ограничений целевой таблицы или представления. Без параметра 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 или text с символьными значениями, коды которых больше 127 или меньше 32. Пример см. в статье об указании кодовой страницы.

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

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

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

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

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

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

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

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

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

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

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

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

Дополнительные сведения см. в разделе Использование собственного формата Юникод для импорта и экспорта данных (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.

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.

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

FIRE_TRIGGERS

Указывает, что при массовом импорте будут выполняться триггеры типа 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 (Transact-SQL).

Дополнительные сведения о хранении значений удостоверения см. в разделе Сохранение значений удостоверения при массовом импорте данных (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.

ORDER ( { column [ 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 поток применит X-блокировку к таблице (в отличие от блокировки 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 и Создание файла форматирования.

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

FIELDTERMINATOR = "field_terminator"

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

ROWTERMINATOR = "row_terminator"

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

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

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

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

Типы данных

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

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

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

Файлы форматирования представляют данные 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 содержит данные в экспоненциальном представлении float, например:

8.0000000000000002E-2 8.0000000000000002E-2

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

Тем не менее BULK INSERT не может выполнить импорт этих данных непосредственно в t_float, так как второй столбец c2 имеет тип данных decimal. Поэтому необходим файл форматирования. В нем данные типа 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 поддерживает только чтение из хранилища BLOB-объектов Azure.

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

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

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

Замечания

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

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

Инструкция 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 Server, см. в статье Предварительные условия для минимального протоколирования массового импорта данных. Минимальный уровень ведения журнала не поддерживается в базе данных SQL Azure.

Ограничения

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

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

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

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

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

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

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

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

Для того чтобы прочитать исходные данные, учетной записи, которая используется службой SQL Server Database Engine, необходимо предоставить доступ к этим исходным данным. В отличие от этого, если пользователь 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 CREDENTIAL на основе ключа SAS, зашифрованного с помощью MASTER KEY, а затем создайте внешний источник базы данных для использования в команде BULK INSERT.

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

Важно!

Управляемое удостоверение применимо только к SQL Azure. SQL Server не поддерживает управляемое удостоверение.

Разрешения

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

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

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

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

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

    Триггеры не срабатывают по умолчанию. Чтобы явно включить триггеры, укажите параметр FIRE_TRIGGER.

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

Примеры

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

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

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

Важно!

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

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

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

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

Важно!

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

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

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

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

Примечание.

Благодаря тому, как Microsoft Windows обрабатывает текстовые файлы, \n автоматически заменяется на \r\n.

Важно!

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

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

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

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

Важно!

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

Д. Импорт данных из 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');

Важно!

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

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 = 'YourStrongPassword1';
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 = 'YourStrongPassword1';
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 не поддерживает управляемое удостоверение.

Важно!

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

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

В следующем примере показано, как загрузить данные из CSV-файла в Хранилище BLOB-объектов Azure, которое было настроено в качестве внешнего источника данных, и определить файл с ошибкой. Для этого вам потребуются учетные данные для базы с подписанным URL-адресом. При выполнении базы данных SQL Azure параметр ERRORFILE должен использоваться вместе с 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 приведены в следующих статьях:

См. также