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

  • Статья

Эта тема посвящена использованию файла форматирования в операциях массового импорта. Файл форматирования сопоставляет поля файла данных столбцам таблицы. При массовом импорте данных можно использовать как формат XML, так и формат, отличный от XML, если импорт выполняется с помощью команды bcp или инструкций BULK INSERT или INSERT... SELECT * FROM OPENROWSET(BULK...) языка Transact-SQL.

Важное примечаниеВажно!

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

Дополнительные сведения по файлам форматирования см. в разделах Файлы формата, отличные от XML (SQL Server) и XML-файлы форматирования (SQL Server).

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

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

Команда массовой загрузки

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

BULK INSERT

FORMATFILE = 'format_file_path'

INSERT... SELECT * FROM OPENROWSET(BULK...).

FORMATFILE = 'format_file_path'

bcp … in

-fformat_file

Дополнительные сведения см. в разделе Программа bcp, BULK INSERT (Transact-SQL) или OPENROWSET (Transact-SQL).

ПримечаниеПримечание

Для массового экспорта или импорта данных SQLXML используется один из следующих типов данных в файле форматирования: SQLCHAR или SQLVARYCHAR (данные посылаются в кодовой странице клиента или в кодовой странице, предполагаемой параметрами сортировки), SQLNCHAR или SQLNVARCHAR (данные посылаются в формате Юникод) и SQLBINARY или SQLVARYBIN (данные посылаются без преобразования).

Примеры

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

Образец таблицы

Для выполнения этих примеров необходимо создать таблицу myTestFormatFiles в схеме dbo образца базы данных AdventureWorks2012. Для создания этой таблицы в редакторе запросов среды Среда SQL Server Management Studio выполните следующий код:

USE AdventureWorks2012;
GO
CREATE TABLE myTestFormatFiles (
   Col1 smallint,
   Col2 nvarchar(50),
   Col3 nvarchar(50),
   Col4 nvarchar(50)
   );
GO

Образец файла данных

В примере используется образец файла данных myTestFormatFiles-c.Dat, который содержит следующие записи. Для создания файла данных введите в командной строке Microsoft Windows:

10,Field2,Field3,Field4
15,Field2,Field3,Field4
46,Field2,Field3,Field4
58,Field2,Field3,Field4

Образцы файлов форматирования

В некоторых примерах в этом разделе используется файл форматирования XML myTestFormatFiles-f-x-c.Xml, в других примерах используются файлы других форматов (не XML). Во всех файлах форматирования содержатся символьные данные и используется нестандартный признак конца поля (,).

Образец файла форматирования в формате, отличном от XML

В следующем примере используется bcp для формирования XML-файла форматирования из таблицы myTestFormatFiles. Файл myTestFormatFiles.Fmt содержит следующие сведения:

9.0
4
1       SQLCHAR       0       7       ","      1     Col1         ""
2       SQLCHAR       0       100     ","      2     Col2         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      3     Col3         SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       100     "\r\n"   4     Col4         SQL_Latin1_General_CP1_CI_AS

Чтобы воспользоваться bcp с параметром format для создания этого файла форматирования, введите в командной строке Windows следующее:

bcp AdventureWorks2012..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T

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

Образец XML-файла форматирования

В следующем примере используется bcp для создания XML-файла форматирования из таблицы myTestFormatFiles. Файл myTestFormatFiles.Xml содержит следующие сведения:

<?xml version="1.0"?>
<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Чтобы воспользоваться bcp с параметром format для создания этого файла форматирования, введите в командной строке Windows следующее:

bcp AdventureWorks2012..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T

Использование команды bcp

В следующем примере инструкция bcp используется для массового импорта данных из файла данных myTestFormatFiles-c.Dat в таблицу HumanResources.myTestFormatFiles в образце базы данных. В примере используется файл форматирования XML MyTestFormatFiles.Xml. Перед импортированием файла данных все существующие строки таблицы удаляются.

В командной строке Windows введите:

bcp AdventureWorks2012..myTestFormatFiles in C:\myTestFormatFiles-c.Dat -f C:\myTestFormatFiles.Xml -T
ПримечаниеПримечание

Дополнительные сведения об этой команде см. в разделе Программа bcp.

Использование предложения BULK INSERT

В следующем примере для массового импортирования данных из файла данных myTestFormatFiles-c.Dat в таблицу HumanResources.myTestFormatFiles образца базы данных AdventureWorks2012 используется инструкция BULK INSERT. В примере используется файл форматирования, отличный от XML, MyTestFormatFiles.Fmt. Перед импортированием файла данных все существующие строки таблицы удаляются.

В редакторе запросов среды Среда SQL Server Management Studio выполните:

USE AdventureWorks2012;
GO
DELETE myTestFormatFiles;
GO
BULK INSERT myTestFormatFiles 
   FROM 'C:\myTestFormatFiles-c.Dat' 
   WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt');
GO
SELECT * FROM myTestFormatFiles;
GO
ПримечаниеПримечание

Дополнительные сведения об этой инструкции см. в разделе BULK INSERT (Transact-SQL).

Использование поставщика массовых наборов строк OPENROWSET

В следующем примере для массового импорта данных из файла данных myTestFormatFiles-c.Dat в таблицу HumanResources.myTestFormatFiles образца базы данных AdventureWorks используется команда INSERT ... SELECT * FROM OPENROWSET(BULK...). В примере используется файл форматирования XML MyTestFormatFiles.Xml. Перед импортированием файла данных все существующие строки таблицы удаляются.

В редакторе запросов среды Среда SQL Server Management Studio выполните:

USE AdventureWorks2012;
DELETE myTestFormatFiles;
GO
INSERT INTO myTestFormatFiles
    SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestFormatFiles-c.Dat',
      FORMATFILE='C:\myTestFormatFiles.Xml'     
      ) as t1 ;
GO
SELECT * FROM myTestFormatFiles;
GO

Закончив эксперименты с образцом таблицы, удалите ее при помощи следующей инструкции:

DROP TABLE myTestFormatFiles
ПримечаниеПримечание

Дополнительные сведения о предложении OPENROWSET BULK см. в разделе OPENROWSET (Transact-SQL).

Дополнительные примеры

Создание файла форматирования (SQL Server)

Пропуск столбца таблицы с помощью файла форматирования (SQL Server)

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

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

См. также

Справочник

Программа bcp

BULK INSERT (Transact-SQL)

OPENROWSET (Transact-SQL)

Основные понятия

Файлы формата, отличные от XML (SQL Server)

XML-файлы форматирования (SQL Server)