BULK INSERT (Transact-SQL)

  • Статья

Изменения: 12 декабря 2006 г.

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

Значок ссылки на разделСинтаксические обозначения в Transact-SQL

Синтаксис

BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
        ( 
   [ [ , ] BATCHSIZE = batch_size ] 
   [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] 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 ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
        )]

Аргументы

  • database_name
    Имя базы данных, в которой находится указанная таблица или представление. Если не указано, предполагается текущая база данных.
  • schema_name
    Имя схемы таблицы или представления. Указание аргумента schema_name необязательно, если схемой по умолчанию для пользователя, выполняющего операцию массового импорта, является схема указанной таблицы или представления. Если аргумент schema не указан и схема по умолчанию для пользователя, выполняющего операцию массового импорта, отличается от схемы таблицы или представления, SQL Server возвращает сообщение об ошибке и операция массового импорта не выполняется.
  • table_name
    Имя таблицы или представления, куда производится массовый импорт данных. Могут указываться только те представления, в которых все столбцы относятся к одной и той же базовой таблице. Дополнительные сведения об ограничениях при загрузке данных в представления см. в разделе INSERT (Transact-SQL).

  • 'data_file'
    Полный путь и имя файла данных, который содержит импортируемые в указанную таблицу или представление данные. Инструкция BULK INSERT может импортировать данные с диска (сетевого, гибкого, жесткого и т.д.).

    Параметр data_file должен содержать действительный путь с того сервера, на котором запущен SQL Server. Если data_file является удаленным файлом, указывайте имя в формате UNC.

  • BATCHSIZE **=**batch_size
    Указывает число строк в одном пакете. Каждый пакет копируется на сервер за одну транзакцию. SQL Server фиксирует или откатывает транзакцию для каждого из пакетов. По умолчанию, все данные, содержащиеся в файле, передаются одним пакетом.

    Дополнительные сведения см. в разделе Управление пакетами для массового импорта данных.

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

    ms188365.note(ru-ru,SQL.90).gifПримечание.
    Ограничения UNIQUE, PRIMARY KEY и NOT NULL проверяются в любом случае.

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

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

    ms188365.note(ru-ru,SQL.90).gifПримечание.
    Параметр MAXERRORS не влияет на проверку ограничений.
    ms188365.note(ru-ru,SQL.90).gifПримечание.
    В SQL Server 2005 в инструкцию BULK INSERT включены новые проверки данных, которые могут привести к прекращению работы существующих сценариев, которые раньше выполнялись при наличии в файле неправильных данных.

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

  • CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }
    Указывает кодовую страницу данных в файле данных. Аргумент CODEPAGE имеет смысл только в том случае, если данные содержат столбцы типа char, varchar или text с символами, коды которых больше 127 или меньше 32.

    Значение аргумента CODEPAGE Описание

    ACP

    Столбцы типа char, varchar или text преобразуются из кодовой страницы ANSI/Microsoft Windows (ISO 1252) в кодовую страницу SQL Server.

    OEM (по умолчанию)

    Столбцы типов данных char, varchar и text преобразуются из системной кодовой страницы OEM в кодовую страницу SQL Server.

    RAW

    Преобразование кодовой страницы не производится. Это самый быстрый режим.

    code_page

    Номер кодовой страницы, например 850.

    Дополнительные сведения см. в разделе Копирование данных между различными параметрами сортировки.

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

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

    ms188365.note(ru-ru,SQL.90).gifПримечание.
    Атрибут FIRSTROW не предназначен для пропуска заголовков столбцов. При пропуске строк компонент SQL Server Database Engine выполняет поиск только в признаках конца поля и не проверяет данные в полях пропущенных строк.

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

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

    Дополнительные сведения см. в разделе Управление выполнением триггеров при массовом импорте данных.

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

    • файл данных содержит больше или меньше столбцов, чем таблица или представление;
    • столбцы расположены в другом порядке;
    • отличаются признаки конца столбцов;
    • имеются какие-либо другие изменения в формате данных. Файлы форматирования обычно создаются с помощью программы bcp и затем при необходимости изменяются в текстовом редакторе. Дополнительные сведения см. в разделе Программа bcp.

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

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

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

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

    ms188365.note(ru-ru,SQL.90).gifПримечание.
    Параметр 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, должно приблизительно совпадать с фактическим числом строк.

    Дополнительные сведения см. в разделе Управление пакетами для массового импорта данных.

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

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

    Дополнительные сведения см. в разделе Управление блокировкой при массовом импорте.

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

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

Замечания

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

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

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

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

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

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

Массовый экспорт или импорт документов SQLXML

Чтобы выполнить массовый экспорт или импорт SQLXML-данных используйте один из следующих типов данных в файле форматирования:

Тип данных Результат

SQLCHAR или SQLVARYCHAR

Данные отправляются в кодовой странице клиента или кодовой странице, определенной параметрами сортировки. Тот же эффект достигается указанием параметра DATAFILETYPE ='char' без указания файла форматирования.

SQLNCHAR или SQLNVARCHAR

Данные отправляются в Юникоде. Тот же эффект достигается указанием параметра DATAFILETYPE = 'widechar' без указания файла форматирования.

SQLBINARY или SQLVARYBIN

Данные отправляются без преобразования.

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

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

ms188365.note(ru-ru,SQL.90).gifПримечание.
В SQL Server версии 7.0 и SQL Server 2000, инструкция BULK INSERT поддерживает преобразование строкового типа в десятичный для строк, представляющих собой числовые значения, которые используют экспоненциальный формат.

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

ms188365.note(ru-ru,SQL.90).gifПримечание.
Файл форматирования представляет данные типа real в виде SQLFLT4, а float — в виде SQLFLT8. Дополнительные сведения о XML-файлах форматирования см. в разделе Синтаксис схемы для XML-файлов форматирования; о файлах форматирования, отличных от XML, см. в разделе Указание типа файлового хранилища с помощью программы bcp.

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

В этом примере используется следующая таблица:

CREATE TABLE t_float(c1 float, c2 decimal (5,4))

Пользователю необходимо выполнить массовый импорт данных в таблицу t_float. Файл данных «C:\t_float-c.dat» содержит данные в экспоненциальном представлении float, например:

8.0000000000000002E-28.0000000000000002E-2

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

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

<?xml version="1.0"?>

<BCPFORMAT xmlns="https://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..t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml');
GO

Разрешения

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

  • Существуют ограничения, и параметр CHECK_CONSTRAINTS не указан.
    ms188365.note(ru-ru,SQL.90).gifПримечание.
    Ограничения отключены по умолчанию. Чтобы проверить ограничения явно, укажите параметр CHECK_CONSTRAINTS.
  • Триггеры существуют, и параметр FIRE_TRIGGER не указан.
    ms188365.note(ru-ru,SQL.90).gifПримечание.
    По умолчанию, триггеры не срабатывают. Чтобы явно включить триггеры, укажите параметр FIRE_TRIGGER.
  • для импорта значений идентификаторов из файла данных указан параметр KEEPIDENTITY.

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

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

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

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

Дополнительные сведения об этих и других вопросах безопасности при использовании инструкции BULK INSERT см. в разделе Массовый импорт данных при помощи инструкции BULK INSERT или OPENROWSET(BULK...).

Примеры

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

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

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

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

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

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

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

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

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

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

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

См. также

Справочник

OPENROWSET (Transact-SQL)
sp_tableoption (Transact-SQL)

Другие ресурсы

Подготовка данных к массовому экспорту или импорту
Форматы данных для импорта или экспорта данных
Файлы форматирования для импорта или экспорта данных
Оптимизация производительности массового импорта данных
Операции массового импорта и массового экспорта
Сценарии массового импорта и экспорта данных
Программа bcp
Использование параметров сортировки SQL
Параллельный импорт данных с блокировкой на уровне таблицы

Справка и поддержка

Получение помощи по SQL Server 2005

Журнал изменений

Версия Журнал

12 декабря 2006 г.
Измененное содержимое
  • Обновлено примечание в описании аргумента FIRSTROW.
  • Исправлено описание параметра CHECK_CONSTRAINTS с указанием на то, что этот параметр приводит к пропуску ограничений CHECK и FOREIGN KEY.

17 июля 2006 г.
Измененное содержимое
  • Обновлено описание аргумента ORDER.

14 апреля 2006 г.
Новое содержимое
  • Добавлен раздел «Дополнительные примеры».
Измененное содержимое
  • Исправлен синтаксис разрешения ADMINISTER BULK OPERATIONS.
  • Обновлены требования для разрешения ALTER TABLE.