Bulk Copy Program

Bulk Copy Program (BCP) — это служебная утилита командной строки для массового копирования данных между экземпляром Microsoft SQL Server и файлом данных в определяемом пользователем формате. BCP позволяет экспортировать данные из таблицы или представления SQL Server в файл, а также импортировать данные из файла в таблицу SQL Server. Утилита является частью клиентских средств SQL Server и распространяется вместе с ними, а также доступна как отдельный пакет для скачивания.

История

Утилита BCP была разработана компанией Sybase для своей реляционной системы управления базами данных (СУБД) Sybase SQL Server. Когда Microsoft приобрела лицензию на исходный код Sybase SQL Server и начала разработку собственной версии (Microsoft SQL Server 4.2), утилита BCP вошла в состав её инструментария. В последующих версиях SQL Server, начиная с SQL Server 7.0 (1998 год), BCP была значительно переработана и расширена, в частности, появилась поддержка собственного формата данных (native format) и форматированных файлов (format files). В SQL Server 2005 (2005 год) была добавлена поддержка массового копирования через язык запросов T-SQL (оператор BULK INSERT), а также улучшена работа с большими объектами (LOB). Актуальная версия BCP (версия 15.0) поставляется с SQL Server 2019 и поддерживает все современные форматы данных, включая XML и JSON.

Функциональность

BCP выполняет две основные операции: экспорт (выгрузку) данных из SQL Server и импорт (загрузку) данных в SQL Server. Утилита работает в режиме командной строки, что позволяет использовать её в скриптах и пакетных файлах для автоматизации процессов.

Экспорт данных

При экспорте BCP считывает данные из указанной таблицы, представления или результата запроса T-SQL и записывает их в файл. Пользователь может задать формат файла (символьный, собственный, Unicode), разделители полей и строк, а также количество строк, которые будут обрабатываться за один раз (размер пакета). Экспорт может быть выполнен как для всей таблицы, так и для выбранных столбцов или строк с помощью запроса.

Импорт данных

При импорте BCP считывает данные из файла и вставляет их в указанную таблицу. Перед импортом таблица должна существовать, а её структура (количество и типы столбцов) должна соответствовать структуре данных в файле. BCP может работать в режиме добавления строк (append) или замены существующих данных (truncate). Утилита поддерживает проверку ограничений целостности (constraints) и выполнение триггеров во время импорта.

Форматы файлов

BCP поддерживает несколько форматов файлов, которые определяют способ представления данных:

• Символьный формат (char) — данные хранятся в текстовом виде, разделённые табуляцией (по умолчанию) или другим разделителем. Каждая строка таблицы соответствует одной строке файла. Этот формат является наиболее универсальным и может быть прочитан любым текстовым редактором.
• Собственный формат (native) — данные хранятся в двоичном виде, соответствующем внутреннему представлению SQL Server. Этот формат обеспечивает максимальную скорость передачи данных, но файлы могут быть нечитаемы для других СУБД.
• Unicode-формат — символьный формат, использующий кодировку UTF-16. Применяется для работы с данными, содержащими символы национальных алфавитов.
• Собственный Unicode-формат — двоичный формат с кодировкой UTF-16 для столбцов символьного типа.

Форматные файлы

Для управления отображением между полями файла и столбцами таблицы BCP использует форматные файлы. Форматный файл (обычно с расширением .fmt) содержит описание каждого поля файла: его порядковый номер, тип данных, длину, разделитель и соответствие столбцу таблицы. Форматные файлы могут быть созданы автоматически с помощью команды bcp ... format nul или написаны вручную. Они позволяют импортировать данные с нестандартной структурой, например, когда количество полей в файле не совпадает с количеством столбцов в таблице.

Команды и синтаксис

Общий синтаксис команды BCP:

`` bcp {имя_таблицы | "запрос"} {in | out | queryout | format} имя_файла [параметры] ``

Ключевые параметры:

• in — импорт данных из файла в таблицу.
• out — экспорт данных из таблицы в файл.
• queryout — экспорт результата запроса T-SQL в файл.
• format — создание форматного файла (без передачи данных).
• -c — использование символьного формата.
• -n — использование собственного формата.
• -w — использование Unicode-формата.
• -N — использование собственного Unicode-формата.
• -t — указание разделителя полей (например, -t, для запятой).
• -r — указание разделителя строк (по умолчанию \n).
• -S — имя сервера и экземпляра SQL Server.
• -U — имя пользователя для аутентификации SQL Server.
• -P — пароль пользователя.
• -T — использование встроенной аутентификации Windows (без указания пароля).
• -b — размер пакета (количество строк, передаваемых за одну операцию).
• -E — сохранение значений идентификационных столбцов (identity) при импорте.
• -F — номер первой строки для импорта (начиная с 1).
• -L — номер последней строки для импорта.
• -e — файл для записи ошибок.

Примеры использования

Экспорт таблицы в символьный файл

`` bcp Sales.Orders out "C:\orders.txt" -c -t, -S localhost\SQLEXPRESS -T ``

Эта команда экспортирует все строки из таблицы Sales.Orders в файл orders.txt, используя символьный формат, запятую в качестве разделителя полей и встроенную аутентификацию Windows.

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

`` bcp Sales.Orders in "C:\orders.txt" -c -t, -S localhost\SQLEXPRESS -T -b 1000 ``

Эта команда импортирует данные из файла orders.txt в таблицу Sales.Orders, используя пакеты по 1000 строк.

Экспорт результата запроса

`` bcp "SELECT OrderID, OrderDate FROM Sales.Orders WHERE Status = 'Shipped'" queryout "C:\shipped_orders.txt" -c -t, -S localhost\SQLEXPRESS -T ``

Производительность и ограничения

BCP является одним из самых быстрых способов массового копирования данных в SQL Server, особенно при использовании собственного формата и больших размеров пакетов. Однако утилита имеет ряд ограничений:

• Не поддерживает автоматическое создание целевой таблицы (таблица должна существовать до начала импорта).
• Не поддерживает преобразование типов данных (например, из строки в число) — данные должны соответствовать типам столбцов таблицы.
• При импорте с проверкой ограничений и триггерами скорость может значительно снижаться.
• Не поддерживает работу с данными, содержащими сложные типы, такие как пространственные данные (geometry, geography) или иерархические данные (hierarchyid), без специальной обработки.

Альтернативы

В современных версиях SQL Server (начиная с 2005 года) для массового копирования данных также доступны:

• Оператор T-SQL BULK INSERT — выполняет импорт данных из файла непосредственно в SQL Server без использования клиентской утилиты.
• Команда OPENROWSET(BULK ...) — позволяет выполнять запросы к файлам данных как к таблицам.
• Мастер импорта и экспорта SQL Server — графический интерфейс для настройки массового копирования.
• Служба Integration Services (SSIS) — платформа для создания сложных процессов загрузки и трансформации данных.

Источники

• Microsoft Docs: Bulk Copy Program (BCP) Utility
• Microsoft SQL Server Books Online: bcp Utility
• Sybase SQL Server Reference Manual, 1989
• Kalen Delaney, "Inside SQL Server 2000", Microsoft Press, 2000