Использование файла форматирования для массового импортирования данных
Эта тема посвящена использованию файла форматирования в операциях массового импортирования. Файл форматирования сопоставляет поля файла данных столбцам таблицы. Microsoft SQL Server 2005 позволяет использовать как не-XML, так и XML-файлы форматирования для массового импортирования с помощью команды bcp или инструкций BULK INSERT или INSERT ... SELECT * FROM OPENROWSET(BULK...) языка Transact-SQL.
Важно! |
---|
Чтобы файл форматирования мог работать с файлом данных в Юникоде, все поля входных данных должны быть представлены в виде текстовых строк в Юникоде (то есть в Юникоде в виде строк фиксированной длины или заканчивающимися символом конца строки). |
Примечание. |
---|
Дополнительные сведения по файлам форматирования см. в разделах Основные сведения о файлах форматирования не в формате XML и Основные сведения о XML-файлах форматирования. |
Параметры файла форматирования для команд массового импортирования
В следующей таблице перечислены параметры файла форматирования для каждой команды массового импортирования.
Команда массовой загрузки | Параметр файла форматирования |
---|---|
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 в образце базы данных AdventureWorks под схемой dbo. Для создания этой таблицы в редакторе запросов среды SQL Server Management Studio выполните следующий код:
USE AdventureWorks;
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 AdventureWorks..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T
Дополнительные сведения о создании файла форматирования см. в разделе Создание файла форматирования.
Образец файла форматирования 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 AdventureWorks..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T
Использование команды bcp
В следующем примере для массового импортирования данных из файла данных myTestFormatFiles-c.Dat
в таблицу HumanResources.myTestFormatFiles образца базы данных AdventureWorks используется команда bcp. В примере используется файл форматирования XML MyTestFormatFiles.Xml
. Перед импортированием файла данных все существующие строки таблицы удаляются.
В командной строке Windows введите:
bcp AdventureWorks..myTestFormatFiles in C:\myTestFormatFiles-c.Dat -f C:\myTestFormatFiles.Xml -T
Примечание. |
---|
Дополнительные сведения об этой команде см. в разделе Программа bcp. |
Применение инструкции BULK INSERT
В следующем примере для массового импортирования данных из файла данных myTestFormatFiles-c.Dat
в таблицу HumanResources.myTestFormatFiles образца базы данных AdventureWorks используется инструкция BULK INSERT. В примере используется файл форматирования, отличный от XML, MyTestFormatFiles.Fmt
. Перед импортированием файла данных все существующие строки таблицы удаляются.
В редакторе запросов среды SQL Server Management Studio выполните:
USE AdventureWorks;
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 AdventureWorks;
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). |
См. также
Основные понятия
Использование файла форматирования для установки соответствия полей столбцам при массовом импорте данных
Основные сведения о файлах форматирования не в формате XML
Основные сведения о XML-файлах форматирования
Другие ресурсы
Программа bcp
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)