Bcp ms sql файл формата
Время прочтения: 5 мин.
Порой перед пользователем встает задача загрузки большого файла в таблицу на SQL Server, чтобы в дальнейшем с ней работать. Ранее уже описывались различные подходы к загрузке файлов на сервер. В этой статье познакомимся и разберем еще одно из средств MS SQL Server, которое позволит решить нашу задачу довольно быстро.
Итак, bcp (bulk copy program) – встроенная консольная утилита, которая применяется для массового перемещения данных между сервером MS SQL и файлом пользователя в необходимом «направлении».
В общем виде синтаксис вызова bcp выглядит следующим образом:
Рассмотрим каждый из представленных выше блоков:
- ключевое слово bcp дает указание для запуска утилиты;
- в блоке указывается заранее созданная на сервере таблица (блок должен содержать полное название таблицы — [БД]).[схема]. [таблица], по структуре она должна соответствовать загружаемому файлу);
- в блоке 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«] – задает разделитель столбцов;
[—k] – определяет значения пустых столбцов — они должны остаться в значении NULL (указание этой команды игнорирует ограничения default, установленные для столбцов в таблице).
Полный перечень команд можно увидеть, если в командной строке набрать «bcp –h» (без
В итоге наша строка для вызова bcp будет выглядеть следующим образом:
Самое время проверить все на практике. Для начала создадим таблицу, которая повторяет структуру файла:
В файле, который необходимо загрузить, содержится около 1 млн строк и 36 столбцов. В качестве разделителя столбцов используется символ «~».
Затем откроем командную строку и вставим наше выражение для утилиты bcp, которое мы сформировали ранее
Нажимаем Enter и наблюдаем.
Как мы видим, загрузка такого объемного файла заняла всего 23 секунды, что достаточно быстро.
Наиболее очевидной альтернативой утилите bcp для пользователя является «Мастер импорта и экспорта SQL Server». Он позволяет осуществлять преобразования данных, которые в bcp недоступны, автоматически создавать таблицу в БД, если её ещё не существует на сервере, и всё это доступно пользователю через графический интерфейс. Но, к сожалению, все его преимущества сходят на нет, когда необходимо переместить большой объем данных.
Чтобы не быть голословными в своих утверждениях, проверим какой объем данных загрузит «Мастер импорта и экспорта SQL Server» за то же время. Для этого пройдем все необходимые шаги в «Мастере» и запустим сформированный пакет.
Как мы видим на двух скриншотах выше, за время работы bcp «Мастера импорта и экспорта SQL Server» смог загрузить всего 13 тыс. строк из общего количества 1 млн строк в файле.
Таким образом, потратив некоторое время для подготовки структуры таблицы на сервере и написание необходимой строки для запуска копирования через bcp, мы получим ощутимый выигрыш в скорости загрузки данных в БД.
This topic illustrates the use of a format file in bulk-import operations. A format file maps the fields of the data file to the columns of the table. Please review Create a Format File (SQL Server) for additional information.
Before you begin
- For a format file to work with a Unicode character data file, all input fields must be Unicode text strings (that is, either fixed-size or character-terminated Unicode strings).
- To bulk export or import SQLXML data, use one of the following data types in your format file:
- SQLCHAR or SQLVARCHAR (the data is sent in the client code page or in the code page implied by the collation)
- SQLNCHAR or SQLNVARCHAR (the data is sent as Unicode)
- SQLBINARY or SQLVARBIN (the data is sent without any conversion).
Example test conditions
The examples of format files in this topic are based on the table and data file defined below.
Sample table
The script below creates a test database and a table named myFirstImport . Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
Sample data file
Using Notepad, create an empty file D:\BCP\myFirstImport.bcp and insert the following data:
Alternatively, you can execute the following PowerShell script to create and populate the data file:
Creating the format files
SQL Server support two types of format file: non-XML format and XML format. The non-XML format is the original format that is supported by earlier versions of SQL Server.
Creating a non-XML format file
Please review Non-XML Format Files (SQL Server) for detailed information. The following command will use the bcp utility to generate a non-xml format file, myFirstImport.fmt , based on the schema of myFirstImport . To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. The format option also requires the -f option. In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. At a command prompt, enter the following command:
Your non-XML format file, D:\BCP\myFirstImport.fmt should look as follows:
Ensure your non-XML format file ends with a carriage return\line feed. Otherwise you will likely receive the following error message:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format fileCreating an XML format file
Please review XML Format Files (SQL Server) for detailed information. The following command will use the bcp utility to create an xml format file, myFirstImport.xml , based on the schema of myFirstImport . To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. The format option always requires the -f option, and to create an XML format file, you must also specify the -x option. In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. At a command prompt, enter the following command:
Your XML format file, D:\BCP\myFirstImport.xml should look as follows:
Using a format file to bulk import data
The examples below use the database, datafile, and format files created above.
Using bcp and Non-XML Format File
At a command prompt, enter the following command:
Using bcp and XML format file
At a command prompt, enter the following command:
Using BULK INSERT and Non-XML Format File
Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
Using BULK INSERT and XML Format File
Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
Using OPENROWSET(BULK. ) and Non-XML Format File
Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
Using OPENROWSET(BULK. ) and XML Format File
Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
Порой перед пользователем встает задача загрузки большого файла в таблицу на SQL Server, чтобы в дальнейшем с ней работать. В этой статье познакомимся и разберем одно из средств MS SQL Server, которое позволит решить нашу задачу довольно быстро.
Итак, bcp (bulk copy program) – встроенная консольная утилита, которая применяется для массового перемещения данных между сервером MS SQL и файлом пользователя в необходимом «направлении».
В общем виде синтаксис вызова bcp выглядит следующим образом:
Рассмотрим каждый из представленных выше блоков:
ключевое слово bcp дает указание для запуска утилиты;
в блоке указывается заранее созданная на сервере таблица (блок должен содержать полное название таблицы — [БД].[схема].[таблица], по структуре она должна соответствовать загружаемому файлу);
· в последнем блоке перечисляются команды, которые определяют работу утилиты bcp.
Остановимся подробнее на блоке и основных командах, которые мы будем использовать:
[-T] – указывает, что утилита bcp будет устанавливать доверительное (trusted) соединение с сервером, т.е. без необходимости указания логина/пароля пользователя;
[-S [server name[\instance name]] – после этой команды указывается сервер и, если это необходимо, конкретный инстанс для подключения;
[-F2] – задает номер строки в файле, с которой начнутся чтение и вставка данных (указываем число 2 для пропуска заголовков столбцов);
[-c] – указывает, что операция вставки будет выполняться с использованием символьного типа данных (char);
[-t «field term»] – задает разделитель столбцов;
[-C ] – задает кодировку для данных;
[-k] – определяет значения пустых столбцов — они должны остаться в значении NULL (указание этой команды игнорирует ограничения default, установленные для столбцов в таблице).
В итоге наша строка для вызова 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 столбцов. В качестве разделителя столбцов используется символ «~».
Официальный дилер МАЗ. Сделаем выгодное предложение. Звоните
Вас обязательно получится найти и купить то что нужно
У нас выгодно. Звоните
Размещение сквозной ссылки
По материалам статьи Curt A. Gilman:
The ABCs of BCPПошаговое руководство для "чайников" в MS SQL Server BCP
1. Введение 2. Основы BCP 3. Быстрое массовое копирование 4. Сравнение BCP и DTS Import/Export Wizard 5. Сравнение BCP и резервирования с восстановлением 6. Примечания автора Bulk copy program (BCP), это специальная утилита командной строки для массового копирования, которая поставляется со всеми изданиями SQL Server. Она незаслуженно редко используется, но является необходимым компонентом инструментария DBA. Хотя эта утилита не имеет своего ярлыка в папке SQL Server, к которой можно перейти из меню Start, и только кратко упоминается в SQL Server Books Online (BOL), программа BCP пережила многочисленные версии SQL Server и даже претерпела незначительные улучшения в его последних реализациях.
Несмотря на не высокую популярность BCP, она остается одним из лучших инструментов для быстро перемещения большого объёма данных между базами SQL Server. Если Вы правильно используете BCP, с её помощью можно перемещать большие наборы данных быстрее, чем с помощью встроенных средств Enterprise Manager и Data Transformation Services (DTS), которые чаще всего применяются для этих целей.
Если Вы плохо знакомый с BCP, ниже будет представлен краткий обзор её основных возможностей. BCP перемещает данные из таблиц базы данных в файлы и наоборот. BCP работает в основном с плоскими файлами, данные в которых разделены табуляцией, или с текстовыми файлами с данными фиксированной длинны. BCP работает также и со специальными файлами, разработанного для SQL Server формата, которые позволяют передавать даже не символьные данные. BCP не умеет создавать объекты базы данных, так что таблицы должны существовать до того, как Вы будете использовать BCP, чтобы перенести в них данные.
Как правило, когда Вы вставляете записи в таблицу, сервер вначале регистрирует эти операции в журнале транзакций (transaction log), а затем уже непосредственно записывает данные в базу. Для больших наборов данных, эта операция требует существенного времени и дискового пространства, т.к. применяется двойная фиксация транзакций. Однако, BCP может импортировать данные в таблицу быстрее чем обычная регистрируемая вставка, потому что, при некоторых обстоятельствах, сервер не будет регистрировать операции с записями в transaction log. Вставка записей в таблицу без регистрации в transaction log называется быстрым массовым копированием (fast bulk copy), и автор более подробно описывает такие операции ниже в статье. Но перед этим предлагается более подробно рассмотреть способы запуска утилиты BCP из командной строкой.Запуск утилит из командной строки Windows NT всё реже используется современными DBA. Для Windows 2000/NT ярлык командной строки "Command Prompt" доступен через меню Start. Если в командной строке набрать: "BCP -h" будет представлен листинг общего синтаксиса использования утилиты:
Листинг показывает пару дюжин параметров, из которых нужно знать только несколько, чтобы усвоить советы, которые представляет автор статьи. Имейте в виду, что параметры командной строки зависят от регистра, так что, например, -n не означает то же самое, что -N. Вообще, синтаксис командной строки определяет следующая строка:
Команда начинается с BCP, затем указывается полностью квалифицированное имя таблицы, обозначенное как dbtable. Полностью квалифицированное имя таблицы содержит имя базы данных, владельца table-object и имя table-object. Например, Northwind.dbo.Employees - это полностью квалифицированное имя таблицы. Вы должны указать направление: in или out. Если Вы используете BCP, чтобы копировать данные из файла в таблицу базы данных, используйте опцию in. Если Вы используете BCP, чтобы копировать данные из таблицы базы данных в файл, используйте опцию out. Синтаксис команды продолжает имя файла - datafile. Имя файла может быть абсолютным, например: C:\Temp\Northwind.dat, или это может быть относительное от текущей папки имя, например: ..\Northwind.dat. (две лидирующие точки указывают, что файл Northwind.dat расположен на одну папку выше в иерархии папок). Если Вы используете BCP с опцией out, создаётся новый файл с указанным именем, который автоматически перезаписывает любого файла с тем же самым именем, если он уже существует. После этого Вы можете разместить любые другие, необходимые параметры утилиты BCP.
Рассмотрим теперь использование некоторых важных параметров BCP. Вы можете использовать опцию -S, чтобы указать имя сервера (не обязательно указывать имя сервера, если Вы запускаете BCP на локальном компьютере SQL Server). Если необходимый экземпляр SQL Server не доступен по заданному по умолчанию протоколу, Вы должны прописать имя сервера как псевдоним для компьютера - клиента в SQL Server Client Network Utility. Для подключения к с серверу через его собственную систему аутентификации, необходимо указать имя пользователя и пароль, которые задаются через параметры -U и -P соответственно. При использовании доверительного подключения, указание через параметры имени пользователя и его пароля не требуется, нужно только указать опцию -T.
Для указания типа файла данных, используйте опцию -n, если файл данных, который Вы хотите копировать, имеет собственный формат SQL Server (native-формат), или опцию -c, если файл должен быть текстовым, имеющим в качестве разделителя табуляцию. Для того, что бы закрепить порядок использования параметров BCP, рассмотрим пример, в котором используется следующая информация о параметрах BCP, таблице базы данных и транзитного файла:· Исходная таблица - Employees, владелец которой dbo. · Таблица находится в базе данных Northwind. · Файл экспорта - текстовый файл Northwind.txt имеющий в качестве разделителей табуляцию. · Файл находится в текущей папке на сервере с именем HOMER, к которому Вы обращаетесь через учетную запись sa (которая без пароля).
Воспользовавшись представленными выше правилами, Вы получите следующий набор параметров для утилиты BCP:
Теперь, чтобы полностью изменить направление потока данных, давайте рассмотрим пример перемещения данных средствами BCP из файла в таблицу базы, и запишем команду, которая будет использовать следующую информацию:
· Исходный файл данных - файл типа native SQL Server, с именем Invoices.dat.
· Файл находится на сервере с именем BART, к которому Вы обращаетесь через доверительное подключение.
· Таблица назначения - Invoices, владелец которой Lisa.
· Таблица находится в базе данных Northwind.В результате Вы должны получить следующую команду:
Эти примеры демонстрируют наиболее типичное использование BCP. Вы можете перемещать большие таблицы или базы данных целиком используя представленные в примерах параметры. С помощью BCP можно выполнять и более сложные задачи, используя текстовые файлы фиксированной длинны или файлы с различными разделителями. Для получения дополнительной информации о параметрах BCP, см. BOL.
Если база данных, в которую копируются данные, отвечает двум важным условиям, BCP может осуществлять наиболее быстрое и эффективное массовое копирование. Во-первых, для базы данных должна быть включена опция массового копирования. Для SQL Server 7.0 откройте Enterprise Manager, щёлкните правой кнопкой мыши по базе данных, в которую или из которой будет осуществляться копирование, и выберите Properties в выпадающем меню. После того, как появляется диалоговое окно Properties, откройте закладку Options, чтобы определить, активизирована ли опция Select into/bulk copy. Для SQL Server 2000 должна быть указана Simple или Bulk-Logged Recovery model. Во вторых, используемые для вставки данных из файла таблицы не должны иметь индексов. В этом также можно убедиться с помощью Enterprise Manager: перейдите к принимающей данные таблице, щёлкните по ней правой кнопкой мыши, и выберете из меню пункт All Tasks, а потом Manage indexes. Вы можете временно удалить имеющиеся индексы в окне Manage indexes. Если таблица новая, Вы можете не создавать индексы, пока не скопируете в неё с помощью BCP данные.
Когда Вы копируете данные в новую базу, эти два условия не сложно выполнить. Однако, для промышленных баз данных, которые интенсивно используют прикладные программы, Вы можете столкнуться с невозможностью использования такого подхода, поскольку BCP не регистрирует вставку строк в transaction log. Вы не сможете полностью откатить изменения в данные, если произойдёт сбой в работе BCP. Поэтому, Вы должны быть внимательным при использовании BCP, когда заполняете таблицы, к которым обращаются прикладные программы, и возможно Вам придется рассмотреть другие возможности для импорта данных. Также Вы должны тщательно обдумать изменение установок промышленной базы данных. Активизация опции базы данных Select into/bulk copy не позволит восстановить операции массового копирования из резервных копий transaction log, сделанных после последнего, полного резервного копирования базы данных. Хотя Вы можете использовать BCP, чтобы передать несколько таблиц в другую базу данных, если Вы хотите осуществить перенос таблиц с максимальной скоростью, таблицы для размещения данных должны удовлетворять этим двум условиям.
Поскольку BCP не создаёт объекты базы данных, чтобы создать таблицы для загрузки данных, Вы должны сгенерировать их SQL-скрипты на исходной базе данных. Вы можете легко сгенерировать эти SQL-скрипты в Enterprise Manager: перейдите в исходную базу данных, щёлкните по ней правой кнопкой мыши и выберите Generate SQL Scripts из меню All Tasks. После этого, выберите необходимые объекты на закладке General и проверьте на закладке Formatting, что для каждого из выбранных объектов в генерируемом скрипте будут только команды CREATE. Если в скрипт попадут команды DROP, это значит, что объекты вначале будут уничтожены, т.ч. лучше удаление объектов сделать вручную.
Кроме того, в закладке Options, нужно проверить, что параметры: Script indexes, Script triggers и Script PRIMARY Keys, FOREIGN Keys, Defaults и Check Constraints активизированы.
После всего этого можно сохранить получившийся SQL-скрипты в файл, щёлкнув OK. Если Вы хотите предварительно увидеть скрипт, щёлкните Preview на вкладке General. И, наконец, чтобы создать необходимые таблицы и другие объекты в базе данных, куда будут копироваться данные, Вы должны запустить там эти SQL-скрипты на исполнение. Т.е. Вы должны открыть Query Analyzer, а затем выполнить команды CREATE TABLE из SQL-скриптов. Лучше сгруппировать команды CREATE TABLE вверху скрипта и выполнить их отдельно, потому что, если выполнить ещё и создание индексов, невозможно будет добиться быстрого массового копирования в новые таблицы.
После того, как Вы создали таблицы в базе данных, Вы можете без опаски использовать BCP для поочерёдного массового копирования таблиц исходной базы данных в файлы. Если Вы копируете данные между SQL серверами, логично использовать родной для них native-формат файлов, который в этом случае будет наиболее эффективен. После этого, все файлы, которые Вы только что создавали, могут быть также поочерёдно загружены в базу данных с вновь созданными таблицами. После завершения копирования данных, Вы можете запустить на исполнение в импортирующей базе данных оставшиеся команды из SQL-скриптов, которые были сгенерированы ранее. Эти команды создадут все ограничения ключей, триггеры и индексы, которые были у исходных таблиц, но не были ещё созданы для новых.Использование BCP для передачи данных не выглядит простой задачей. Резонно задать вопрос: "Существует ли более простой метод?". В SQL Server 7.0 и 2000 альтернативным методом передачи данных является использование DTS. DTS имеет много удобных свойств, таких, как графический интерфейс. Ярлык DTS (Import and Export Data) также можно найти через меню Start, и он запускает Import/Export Wizard. Эта утилита имеет в своём арсенале несколько удобных визардов интегрированных с Enterprise Manager, и обладает более широкими возможностями, а также может осуществлять более сложные преобразования данных, которые не доступны с помощью BCP. Также Вы можете использовать DTS для передачи данных между гетерогенными СУБД. На первый взгляд может показаться не понятным почему, если DTS имеет такие большие возможности, автор статьи рекомендует использовать BCP для копирования данных? Главным преимуществом использования для этого BCP, а не DTS Import/Export Wizard, является скорость передачи данных. Import/Export Wizard больше подходит для перемещения не большого количества данных или для одновременного преобразования данных. Поскольку этот визард часто требует меньшего времени для выполнения предварительных настроек копирования, он более удобен для небольших наборов данных. Однако, этот выигрыш во времени перестаёт быть решающим, когда наборы данных станут большими, и BCP начнёт превосходить его по быстродействию. Также, визард не может копировать данные без регистрации в журнале транзакций, что сказывается на увеличении времени его работы. Однако, наиболее важной причиной того, что автор статьи старается не использовать DTS, это то, что он (с настройками по умолчанию) не всегда создает таблицы, которая точно дублирует исходные таблицы. Могут быть утеряны ограничения, индексы и identity. Эта проблема не является критичной, если Вы передаёте не много и маленькие таблицы. Однако, при перемещении или копировании базы данных, Вы должны гарантировать, что вся база данных будет передана так, как она была изначально определена. Используя BCP и SQL-скрипты, которые генерирует Enterprise Manager, Вы создаете новые таблицы точно такими же, как исходные таблицы, наряду с их ограничениями, индексами и identity. Поскольку данные, которые перемещаются, будут идентичны, то и каждые пары таблиц в старой и новой базах будут функционально эквивалентны.
SQL Server имеет и другую, альтернативную BCP возможность перемещения данных путём её резервного копирования и последующего восстановления на другом сервере. Все необходимые операции можно легко сделать с помощью соответствующих визардов Enterprise Manager. В результате Вы можете очень быстро получить точную копию исходной базы данных. К сожалению, восстановление резервной копии базы данных лишает Вас возможности полностью управлять этим процессом. Поскольку новая база данных будет в точности такой же, как оригинальная, все ошибки, которые Вы сделали в исходной базе данных, перекочуют и в новую. Если у старой базы transaction log занимал сотни мегабайт, он станет такого же размера в восстанавливаемой базе данных. Все индексы, который Вы не переиндексировали, останутся в новой базе данных в таком же виде. Кроме того, таким методом Вы на сможете копировать только выборочные объекты. Восстанавливается или всё, или ничего. Использование BCP и SQL-скриптов, которые генерирует Enterprise Manager, позволяет существенно уменьшить занимаемое таблицами место за счёт отсутствия фрагментации, а создание индексов после импорта данных позволит сделать их использование наиболее оптимальным. Кроме того, применение BCP не приведёт к разрастанию журнала транзакций. Используя BCP, Вы можете передавать только необходимые объекты между базами данных.
1. При передаче всех таблиц базы данных вы можете использовать возможности системной хранимой процедуры: sp_MSforeachtable, например:
Можно конечно обойтись и без sp_MSforeachtable, тогда стоит использовать конструкцию на подобии представленной ниже, которая создаёт файлы в csv-формате:
2. При передаче больших по размеру таблиц, можно получить дополнительный выигрыш во времени за счёт использования параметра BCP: [-b batchsize]. Подробнее об этом параметре можно прочитать в SQL Server Books Online, поиск по ключевым словам: Batch Switches.
The Bulk copy program aka bcp is the console application used to export and import data from text files to SQL Server or vice versa.
It is very popular because it is fast and easy to download.
This tool is installed by default with SQL Server. It is usually installed in the following path:
Drive: \\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe
In this article we will learn how to:
- Get the bcp arguments
- Get the version
- Export data from a SQL Server table to a file
- Export data from a SQL Server query to a file
- Run bcp using PowerShell
- Run bcp on SSIS
- Invoke a batch file in SSIS
Requirements
- SQL Server installed installed
- The Adventureworks database installed
- SSIS Installed
- SSDT installed
Get started
1. Get the bcp arguments
In the command line, write bcp. This will show you the possible arguments that can be used:
bcp /? Is the same than bcp. It shows you the possible parameters:
2. Get the version
You can get the version of the bcp using the -v argument:
Here you have a list of versions of bcp:
bcp version SQL Server 14.0.X.X SQL Server 2017 13.0.X.X SQL Server 2016 12.0.X.X SQL Server 2014 11.0.X.X SQL Server 2012 10.0.X.X SQL Server 2008 9.0.X.X SQL Server 2005 8.0.X.X SQL Server 2000 7.0.X.X SQL Server 7 3. Export data from a SQL Server table to a file
To export data from a SQL Server table or view to a file, you can use the bcp command.
The following example exports the table person.person from the adventureworks database to a file named dimcustomer.bcp
bcp adventureworks.person.person out c:\sql\dimcustomer.bcp -c -T -S WIN-355CHQ0E524
The -c argument is used to perform operations using a character type. -T is used to connect using a Trusted connection (Windows Authentication). -S is used to specify the SQL Server name.
If everything is OK, the rows will be copied successfully:
You will receive the number of rows copied, the network packet size and the speed to copy the rows per second.
If everything is OK, the file will be created:
You can open the file and check the data:
4. Export data from a SQL Server query to a file
You can export data from a SQL Server Query to a file. To do this, you need to specify the query in quotes and use the out argument:
bcp adventureworks.person.person out c:\sql\dimcustomer.bcp -c -T -S WIN-355CHQ0E524
The result displayed is the following:
5. Run bcp using PowerShell
PowerShell is a powerful tool to automate tasks using scripts and the command line. You can also run bcp using PowerShell, which is included with Windows and it can be also installed on Linux and Mac.
In PowerShell create variables to store the database name, schema, table and output path:
Читайте также: