Время прочтения: 5 мин.

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

Итак, bcp (bulk copy program) – встроенная консольная утилита, которая применяется для массового перемещения данных между сервером MS SQL и файлом пользователя в необходимом «направлении».

В общем виде синтаксис вызова bcp выглядит следующим образом:

bcp {data table} {in | out | queryout | format} {data file} {options}

Рассмотрим каждый из представленных выше блоков:

  • ключевое слово bcp дает указание для запуска утилиты;
  • в блоке {data table} указывается заранее созданная на сервере таблица (блок должен содержать полное название таблицы — [БД]).[схема]. [таблица], по структуре она должна соответствовать загружаемому файлу);
  • в блоке {in | out | queryout | format} указывается то самое «направление» для перемещения данных (для загрузки файла будем использовать in);
  • в блоке {data file} указывается полный путь к файлу;
  • в последнем блоке {options} перечисляются команды, которые определяют работу утилиты bcp.

Остановимся подробнее на блоке {options} и основных командах, которые мы будем использовать:

[—T] – указывает, что утилита bcp будет устанавливать доверительное (trusted) соединение с сервером, т.е. без необходимости указания логина/пароля пользователя;

[—S [server name[\instance name]] – после этой команды указывается сервер и, если это необходимо, конкретный инстанс для подключения;

[—F2] – задает номер строки в файле, с которой начнутся чтение и вставка данных (указываем число 2 для пропуска заголовков столбцов);

[—c] – указывает, что операция вставки будет выполняться с использованием символьного типа данных (char);

[—t «field term«] – задает разделитель столбцов;

[—C {ACP | OEM | RAW | codepage}] – задает кодировку для данных;

[—k] – определяет значения пустых столбцов — они должны остаться в значении NULL (указание этой команды игнорирует ограничения default, установленные для столбцов в таблице).

Полный перечень команд можно увидеть, если в командной строке набрать «bcp –h» (без

кавычек), также он доступен из официальной документации Microsoft (https://docs.microsoft.com/ru—ru/sql/tools/bcp—utility?view=sql—server—ver15#k).

            В итоге наша строка для вызова bcp будет выглядеть следующим образом:

bcp [TB44_SANDBOX].[mis].[test_table_bcp] in "C:\Users\UserName\Documents\datafile.csv" -T -S hyperion.ca.sbrf.ru\hyperion -F2 -c -t"~" -C ACP –k

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

CREATE TABLE [TB44_SANDBOX].[mis].[test_table_bcp]
(
	[client_id] [nvarchar](500) NULL,
	[npackid] [nvarchar](500) NULL,
	[application_num] [nvarchar](500) NULL,
	[application_date] [nvarchar](500) NULL,
	[check_date] [nvarchar](500) NULL,
	[application_end_date] [nvarchar](500) NULL,
	[npackid_prev] [nvarchar](500) NULL,
	[c_num_decl] [nvarchar](500) NULL,
	[c_date_close] [nvarchar](500) NULL,
	[create_doc_prev] [nvarchar](500) NULL,
	[contract_num_prev] [nvarchar](500) NULL,
	[cred_sum_prev] [nvarchar](500) NULL,
	[annuity_date] [nvarchar](500) NULL,
	[annuity_summ] [nvarchar](500) NULL,
	[cred_percent_prev] [nvarchar](500) NULL,
	[dosr] [nvarchar](500) NULL,
	[next_annuity_summ] [nvarchar](500) NULL,
	[client_birth] [nvarchar](500) NULL,
	[sozaim] [nvarchar](500) NULL,
	[month_payment_ods] [nvarchar](500) NULL,
	[cred_percent_prev_ods] [nvarchar](500) NULL,
	[last_upd_date_ods] [nvarchar](500) NULL,
	[liab_appnum_ods] [nvarchar](500) NULL,
	[residual_debt_ods] [nvarchar](500) NULL,
	[liab_credit_type_ods] [nvarchar](500) NULL,
	[ref14] [nvarchar](500) NULL,
	[begin_date] [nvarchar](500) NULL,
	[end_date] [nvarchar](500) NULL,
	[loan_term] [nvarchar](500) NULL,
	[annuit_payment_calculated] [nvarchar](500) NULL,
	[predpens] [nvarchar](500) NULL,
	[btw] [nvarchar](500) NULL,
	[new_cash] [nvarchar](500) NULL,
	[annuity_count] [nvarchar](500) NULL,
	[payment_diff] [nvarchar](500) NULL,
	[payment_diff1] [nvarchar](500) NULL
) ON [PRIMARY] WITH(DATA_COMPRESSION=PAGE)

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

Затем откроем командную строку и вставим наше выражение для утилиты bcp, которое мы сформировали ранее

Нажимаем Enter и наблюдаем.

Как мы видим, загрузка такого объемного файла заняла всего 23 секунды, что достаточно быстро.

            Наиболее очевидной альтернативой утилите bcp для пользователя является «Мастер импорта и экспорта SQL Server». Он позволяет осуществлять преобразования данных, которые в bcp недоступны, автоматически создавать таблицу в БД, если её ещё не существует на сервере, и всё это доступно пользователю через графический интерфейс. Но, к сожалению, все его преимущества сходят на нет, когда необходимо переместить большой объем данных.

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

Как мы видим на двух скриншотах выше, за время работы bcp «Мастера импорта и экспорта SQL Server» смог загрузить всего 13 тыс. строк из общего количества 1 млн строк в файле.

Таким образом, потратив некоторое время для подготовки структуры таблицы на сервере и написание необходимой строки для запуска копирования через bcp, мы получим ощутимый выигрыш в скорости загрузки данных в БД.