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

  • Статья

Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)

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

Примечание

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

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

  1. Массовый экспорт данных из исходной таблицы в файл данных.

  2. Массовый импорт данных из файла данных в целевую таблицу.

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

Ограничения

Чтобы успешно импортировать данные в собственный формат, убедитесь, что:

  • Файл данных создан в собственном формате.

  • Либо целевая таблица должна быть совместима с файлом данных (иметь верное число столбцов, типы данных, длину, допустимость значений NULL и так далее), либо необходимо использовать файл форматирования для сопоставления каждого поля с соответствующим столбцом.

    Примечание

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

    Сведения об использовании файлов форматирования см. в разделе Формат файлов для импорта или экспорта данных (SQL Server).

Успешный импорт не приводит к повреждению целевой таблицы.

Как bcp обрабатывает данные в собственном формате

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

  • Несимвольные данные.

    Программа bcp использует для записи несимвольных данных из таблицы в файл данных внутренний двоичный формат данных SQL Server .

  • Данные типаchar или varchar .

    В начале каждого поля char или varchar программа bcp добавляет длину префикса.

    Важно!

    По умолчанию при использовании собственного режима программа bcp перед копированием в файл данных преобразует символы из формата SQL Server в символы OEM. Программа bcp преобразует символы из файла данных в символы ANSI перед их массовым импортом в таблицу SQL Server . Во время подобных преобразований расширенные символьные данные могут быть потеряны. Для расширенных наборов символов необходимо либо использовать собственный формат в Юникоде, либо задать кодовую страницу.

  • sql_variant data

    Если данные типа sql_variant сохраняются как SQLVARIANT в файле данных в собственном формате, то все характеристики данных сохраняются. Метаданные, в которых записан тип данных каждой величины, записываются вместе со значениями данных. Эти метаданные используются для повторного создания значений данных с тем же типом данных, что и в столбце назначения sql_variant .

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

    Дополнительные сведения о преобразовании данных см. в разделе Преобразование типов данных (ядро СУБД).

Параметры командной строки для собственного формата

Вы можете импортировать данные собственного формата в таблицу с помощью bcp, BULK INSERT или INSERT... SELECT * FROM OPENROWSET(BULK...). Для команды bcp или инструкции BULK INSERT можно указать формат данных в инструкции . Для инструкции INSERT ... SELECT * FROM OPENROWSET(BULK...) нужно указать формат данных в файле форматирования.

Собственный формат поддерживается следующими параметрами командной строки:

Get-Help Параметр Описание
bcp -n Приводит к использованию служебной программой bcp собственных типов данных. *
BULK INSERT DATAFILETYPE ="native" Использует собственный тип данных или расширенный собственный тип данных. Учтите, что параметр DATAFILETYPE не нужен, если типы данных указываются в файле форматирования.
OPENROWSET Недоступно Требуется использовать файл форматирования.

*Чтобы загрузить собственные данные (-n) в формат, совместимый с более ранними версиями SQL Server клиентов, используйте параметр -V. Дополнительные сведения см. в разделе Импорт данных в собственном и символьном формате из предыдущих версий SQL Server.

Примечание

Также в файле форматирования можно указать форматирование для каждого поля. Дополнительные сведения см. в статье Файлы форматирования для импорта или экспорта данных (SQL Server).

Пример условий теста

Примеры в этой статье основаны на таблице и файле форматирования, которые определены ниже.

Пример таблицы

Приведенный ниже скрипт создает тестовую базу данных, таблицу с именем myNative и заполняет таблицу начальными значениями. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myNative ( 
   PersonID smallint NOT NULL,
   FirstName varchar(25) NOT NULL,
   LastName varchar(30) NOT NULL,
   BirthDate date,
   AnnualSalary money
   );

-- Populate table
INSERT TestDatabase.dbo.myNative
VALUES 
(1, 'Anthony', 'Grosse', '1980-02-23', 65000.00),
(2, 'Alica', 'Fatnowna', '1963-11-14', 45000.00),
(3, 'Stella', 'Rossenhain', '1992-03-02', 120000.00);

-- Review Data
SELECT * FROM TestDatabase.dbo.myNative;

Пример файла форматирования в формате, отличном от XML

SQL Server поддерживает два типа файлов форматирования: файлы форматирования в формате, отличном от XML, и XML-файлы форматирования. Файл форматирования не в формате XML поддерживается более ранними версиями SQL Server. Дополнительные сведения см. в разделе Файлы формата, отличные от XML (SQL Server) . Следующая команда будет использовать служебную программу bcp для создания файла форматирования myNative.fmtв формате, отличном от XML, на основе схемы myNative. Чтобы создать файл форматирования с помощью служебной программы bcp , укажите аргумент format , а вместо пути файла данных задайте значение nul . Параметр format также требует наличия параметра -f . Кроме того, в этом примере квалификатор c используется для указания символьных данных, а T используется для указания доверенного подключения, в рамках которого применяется встроенная система безопасности. В командной строке введите следующие команды:

bcp TestDatabase.dbo.myNative format nul -f D:\BCP\myNative.fmt -T 

REM Review file
Notepad D:\BCP\myNative.fmt

Важно!

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

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

Примеры

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

Использование bcp и собственного формата для экспорта данных

Параметр -n и команда OUT . Примечание. Файл данных, созданный в этом примере, будет использоваться во всех последующих примерах. В командной строке введите следующие команды:

bcp TestDatabase.dbo.myNative OUT D:\BCP\myNative.bcp -T -n

REM Review results
NOTEPAD D:\BCP\myNative.bcp

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

Параметр -n и команда IN . В командной строке введите следующие команды:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNative;"

REM Import data
bcp TestDatabase.dbo.myNative IN D:\BCP\myNative.bcp -T -n

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNative;"

Использование bcp и собственного формата для импорта данных с файлом форматирования в формате, отличном от XML

Параметры -n и -f switches и IN commи. В командной строке введите следующие команды:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNative;"

REM Import data
bcp TestDatabase.dbo.myNative IN D:\BCP\myNative.bcp -f D:\BCP\myNative.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNative;"

Использование инструкции BULK INSERT и собственного формата без файла форматирования

АргументDATAFILETYPE . Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management Studio (SSMS):

TRUNCATE TABLE TestDatabase.dbo.myNative; -- for testing
BULK INSERT TestDatabase.dbo.myNative
	FROM 'D:\BCP\myNative.bcp'
	WITH (
		DATAFILETYPE = 'native'
		);

-- review results
SELECT * FROM TestDatabase.dbo.myNative;

Использование инструкции BULK INSERT и собственного формата с файлом форматирования в формате, отличном от XML

АргументFORMATFILE . Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management Studio (SSMS):

TRUNCATE TABLE TestDatabase.dbo.myNative; -- for testing
BULK INSERT TestDatabase.dbo.myNative
   FROM 'D:\BCP\myNative.bcp'
   WITH (
		FORMATFILE = 'D:\BCP\myNative.fmt'
		);

-- review results
SELECT * FROM TestDatabase.dbo.myNative;

Использование инструкции OPENROWSET и собственного формата с файлом форматирования в формате, отличном от XML

АргументFORMATFILE . Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management Studio (SSMS):

TRUNCATE TABLE TestDatabase.dbo.myNative;  -- for testing
INSERT INTO TestDatabase.dbo.myNative
	SELECT *
	FROM OPENROWSET (
		BULK 'D:\BCP\myNative.bcp', 
		FORMATFILE = 'D:\BCP\myNative.fmt'  
		) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myNative;

Использование форматов данных для массового импорта или экспорта

См. также раздел

Программа bcp
BULK INSERT (Transact-SQL)
Типы данных (Transact-SQL)
sql_variant (Transact-SQL)
Импорт данных в собственном и символьном формате из предыдущих версий SQL Server
OPENROWSET (Transact-SQL)
Использование собственного формата Юникода для импорта или экспорта данных (SQL Server)