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

Применяется к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureAzure Synapse AnalyticsСистема аналитической платформы (PDW)

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

В родном формате сохраняются родные типы данных базы данных. Собственный формат предназначен для высокоскоростной передачи данных между таблицами 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 данные

  Если данные типа 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...) нужно указать формат данных в файле форматирования.

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

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

Примеры

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

Использование 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 переключатели и команда 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 -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;

Связанные задачи

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

• Импорт данных в собственном и символьном формате из предыдущих версий SQL Server

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

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

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

См. также

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