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

Статья
06/13/2017

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

Важно!

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

Примечание

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

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

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

Команда массовой загрузки	Параметр файла форматирования
BULK INSERT	FORMATFILE = 'путь_к_файлу_форматирования'
Инструкции INSERT ... SELECT * FROM OPENROWSET(BULK...).	FORMATFILE = 'путь_к_файлу_форматирования'
bcp ... В	-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 в образце базы данных AdventureWorks2012 в схеме dbo . Чтобы создать эту таблицу, в 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 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

В следующем примере функция BULK INSERT используется для массового импорта данных из myTestFormatFiles-c.Dat файла данных в HumanResources.myTestFormatFiles таблицу в образце базы данных AdventureWorks2012. В примере используется файл форматирования, отличный от 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

В следующем примере для массового импорта данных из файла данных INSERT ... SELECT * FROM OPENROWSET(BULK...) в таблицу myTestFormatFiles-c.Dat образца базы данных HumanResources.myTestFormatFiles используется команда AdventureWorks. В примере используется файл форматирования 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

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