Использование файла форматирования для пропуска поля данных (SQL Server)Use a format file to skip a data field (SQL Server)

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure даAzure Synapse Analytics (хранилище данных SQL) даParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Количество полей в файле данных может превышать количество столбцов в таблице.A data file can contain more fields than the number of columns in the table. В этом подразделе описан процесс изменения файлов форматирования в форматах XML и не XML с целью сопоставления столбцов таблицы с полями файла данных и пропуска остальных полей.This topic describes modifying both non-XML and XML format files to accommodate a data file with more fields by mapping the table columns to the corresponding data fields and ignoring the extra fields. Ознакомьтесь с разделом Создание файла форматирования (SQL Server) для получения дополнительных сведений.Please review Create a Format File (SQL Server) for additional information.

КонтурOutline
Пример условий тестаExample Test Conditions
 ● Образец таблицы ● Sample Table
 ● Образец файла данных ● Sample Data File
Создание файлов форматированияCreating the Format Files
 ● Создание файла форматирования в формате, отличном от XML ● Creating a Non-XML Format File
 ● Изменение файла форматирования в формате, отличном от XML ● Modifying a Non-XML Format File
 ● Создание XML-файла форматирования ● Creating an XML Format File
 ● Изменение файла форматирования в формате, отличном от XML ● Modifying an XML Format File
Импорт данных с помощью файла форматирования для пропуска поля данныхImporting Data with a Format File to skip a Data Field
 ● Использование bcp и файла форматирования в формате, отличном от XML ● Using bcp and Non-XML Format File
 ● Использование bcp и XML-файла форматирования ● Using bcp and XML Format File
 ● Использование BULK INSERT и файла форматирования в формате, отличном от XML ● Using BULK INSERT and Non-XML Format File
 ● Использование BULK INSERT и XML-файла форматирования ● Using BULK INSERT and XML Format File
 ● Использование OPENROWSET(BULK…) и файла форматирования в формате, отличном от XML ● Using OPENROWSET(BULK...) and Non-XML Format File
 ● Использование OPENROWSET(BULK…) и XML-файла форматирования ● Using OPENROWSET(BULK...) and XML Format File

Примечание

И файлы форматирования, отличные от XML, и XML-файлы форматирования можно использовать для массового импорта файла данных в таблицу с помощью команды служебной программы bcp, а также инструкции BULK INSERT или INSERT... SELECT * FROM OPENROWSET(BULK...).Either a non-XML or XML format file can be used to bulk import a data file into the table by using a bcp utility command, BULK INSERT statement, or INSERT ... SELECT * FROM OPENROWSET(BULK...) statement. Дополнительные сведения см. в разделе Использование файла форматирования для массового импорта данных (SQL Server).For more information, see Use a Format File to Bulk Import Data (SQL Server).

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

Примеры измененных файлов форматирования базируются на таблице и файле данных, которые определены ниже.The examples of modified format files in this topic are based on the table and data file defined below.

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

Приведенный ниже сценарий создает тестовую базу данных и таблицу с именем myTestSkipField.The script below creates a test database and a table named myTestSkipField. Выполните следующий код Transact-SQL в Microsoft SQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

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

Создайте пустой файл D:\BCP\myTestSkipField.bcp и вставьте следующие данные:Create an empty file D:\BCP\myTestSkipField.bcp and insert the following data:

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

Создание файлов форматированияCreating the Format Files

Для массового импорта из файла myTestSkipField.bcp в таблицу myTestSkipField файл форматирования должен сделать следующий код:To bulk import data from myTestSkipField.bcp into the myTestSkipField table, the format file must do the following:

  • сопоставить первое поле данных с первым столбцом, PersonID;Map the first data field to the first column, PersonID.
  • пропустить второе поле данных;Skip the second data field.
  • сопоставить третье поле данных со вторым столбцом, FirstName;Map the third data field to the second column, FirstName.
  • сопоставить четвертое поле данных с третьим столбцом, LastName.Map the fourth data field to the third column, LastName.

Простейший способ создать файл форматирования заключается в использовании служебной программы bcp.The simplest method to create the format file is by using the bcp utility. Во-первых, создайте базовый файл форматирования из существующей таблицы.First, create a base format file from the existing table. Во-вторых, измените его в соответствии с фактическим файлом данных.Second, modify the base format file to reflect the actual data file.

Создание файла форматирования в формате, отличном от XMLCreating a Non-XML Format File

Дополнительные сведения см. в разделе Файлы формата, отличные от XML (SQL Server) .Please review Non-XML Format Files (SQL Server) for detailed information. Следующая команда будет использовать служебную программу bcp для создания файла форматирования myTestSkipField.fmt, отличного от XML, на основе схемы myTestSkipField.The following command will use the bcp utility to generate a non-xml format file, myTestSkipField.fmt, based on the schema of myTestSkipField. Кроме того, квалификатор c используется для указания символьных данных, t, используется для указания запятую в качестве признака конца поля, а T используется для указания доверенного соединения с использованием встроенной системы безопасности.In addition, the qualifier c is used to specify character data , t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. В командной строке введите следующую команду:At a command prompt, enter the following command:

bcp TestDatabase.dbo.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

Изменение файла форматирования в формате, отличном от XML Modifying the Non-XML Format File

Терминологию см. в разделе Структура файлов форматирования в формате, отличном от XML .See Structure of Non-XML Format Files for terminology. Откройте D:\BCP\myTestSkipField.fmt в Блокноте и внесите следующие изменения:Open D:\BCP\myTestSkipField.fmt in Notepad and perform the following modifications:

  1. Скопируйте всю строку для FirstName из файла форматирования и вставьте ее на следующей строке после FirstName .Copy the entire format-file row for FirstName and paste it directly after FirstName on the next line.
  2. Увеличьте значения порядка полей файла узлов на единицу для новой строки и всех последующих строк.Increase the host file field order value by one for the new row and all subsequent rows.
  3. Увеличьте значение числа столбцов в соответствии с действительным числом полей в файле данных.Increase the number of columns value to reflect the actual number of fields in the data file.
  4. Измените порядок столбцов сервера с 2 на 0 во второй строке файла форматирования.Modify the server column order from 2 to 0 for the second format-file row.

Сравните внесенные изменения:Compare the changes made:
ПередBefore

13.0
3
1       SQLCHAR 0       7       ","      1     PersonID     ""
2       SQLCHAR 0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR 0       30      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

ПослеAfter

13.0
4
1       SQLCHAR 0       7       ","      1     PersonID     ""
2       SQLCHAR 0       25      ","      0     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR 0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR 0       50      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

Теперь измененный файл форматирования отображает:The modified format file now reflects:

  • 4 поля данных4 data fields
  • Первое поле данных в myTestSkipField.bcp сопоставляется с первым столбцом. myTestSkipField.. PersonIDThe first data field in myTestSkipField.bcp is mapped to the first column, myTestSkipField.. PersonID
  • Второе поле данных в myTestSkipField.bcp не сопоставляется ни с одним из столбцов.The second data field in myTestSkipField.bcp is not mapped to any column.
  • Третье поле данных в myTestSkipField.bcp сопоставляется со вторым столбцом. myTestSkipField.. FirstNameThe third data field in myTestSkipField.bcp is mapped to the second column, myTestSkipField.. FirstName
  • Четвертое поле данных в myTestSkipField.bcp сопоставляется с третьим столбцом. myTestSkipField.. LastNameThe fourth data field in myTestSkipField.bcp is mapped to the third column, myTestSkipField.. LastName

Создание файла форматирования в формате, отличном от XML Creating an XML Format File

Дополнительные сведения см. в разделе XML-файлы форматирования (SQL Server) .Please review XML Format Files (SQL Server) for detailed information. Следующая команда будет использовать служебную программу bcp для создания XML-файла форматирования myTestSkipField.xmlна основе схемы myTestSkipField.The following command will use the bcp utility to create an xml format file, myTestSkipField.xml, based on the schema of myTestSkipField. Кроме того, квалификатор c используется для указания символьных данных, t, используется для указания запятую в качестве признака конца поля, а T используется для указания доверенного соединения с использованием встроенной системы безопасности.In addition, the qualifier c is used to specify character data , t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. Для создания XML-файла форматирования должен использоваться квалификатор x .The x qualifier must be used to generate an XML-based format file. В командной строке введите следующую команду:At a command prompt, enter the following command:

bcp TestDatabase.dbo.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.xml -t, -T

Изменение XML-файла форматирования Modifying the XML Format File

Терминологию см. в разделе Синтаксис схемы для XML-файлов форматирования .See Schema Syntax for XML Format Files for terminology. Откройте D:\BCP\myTestSkipField.xml в Блокноте и внесите следующие изменения:Open D:\BCP\myTestSkipField.xml in Notepad and perform the following modifications:

  1. Скопируйте все второе поле и вставьте его сразу после второго поля на следующей строке.Copy the entire second field and paste it directly after the second field on the next line.
  2. Увеличьте значение FIELD ID на 1 для нового поля и всех последующих полей.Increase the "FIELD ID" value by 1 for the new FIELD and for each subsequent FIELD.
  3. Увеличьте значение COLUMN SOURCE на 1 для FirstNameи LastName в соответствии с исправленным сопоставлением.Increase the "COLUMN SOURCE" value by 1 for FirstName, and LastName to reflect the revised mapping.

Сравните внесенные изменения:Compare the changes made:
ПередBefore

\<?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="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  \<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  \<COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  \<COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  \<COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

ПослеAfter

\<?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="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  \<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  \<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  \<COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  \<COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  \<COLUMN SOURCE="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

Теперь измененный файл форматирования отображает:The modified format file now reflects:

  • 4 поля данных4 data fields
  • Поле 1, соответствующее столбцу 1, сопоставляется с первым столбцом таблицы. myTestSkipField.. PersonIDFIELD 1 which corresponds to COLUMN 1 is mapped to the first table column, myTestSkipField.. PersonID
  • Поле 2, не соответствующее ни одному из столбцов поэтому не сопоставляется ни с одним столбцом таблицы.FIELD 2 does not correspond to any COLUMN and thus, is not mapped to any table column.
  • Поле 3, соответствующее столбцу 3, сопоставляется со вторым столбцом таблицы. myTestSkipField.. FirstNameFIELD 3 which corresponds to COLUMN 3 is mapped to the second table column, myTestSkipField.. FirstName
  • Поле 4, соответствующее столбцу 4, сопоставляется с третьим столбцом таблицы. myTestSkipField.. LastNameFIELD 4 which corresponds to COLUMN 4 is mapped to the third table column, myTestSkipField.. LastName

Импорт данных с помощью файла форматирования для пропуска поля данныхImporting Data with a Format File to skip a Data Field

В приведенных ниже примерах используется база данных, файл данных и файлы форматирования, созданные ранее.The examples below use the database, datafile, and format files created above.

Использование bcp и файла форматирования в формате, отличном от XMLUsing bcp and Non-XML Format File

В командной строке введите следующую команду:At a command prompt, enter the following command:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T

Использование bcp и XML-файла форматированияUsing bcp and XML Format File

В командной строке введите следующую команду:At a command prompt, enter the following command:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.xml -T

Использование BULK INSERT и файла форматирования в формате, отличном от XMLUsing BULK INSERT and Non-XML Format File

Выполните следующий код Transact-SQL в Microsoft SQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField   
   FROM 'D:\BCP\myTestSkipField.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.fmt');  
GO  

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

Использование BULK INSERT и XML-файла форматированияUsing BULK INSERT and XML Format File

Выполните следующий код Transact-SQL в Microsoft SQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField   
   FROM 'D:\BCP\myTestSkipField.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.xml');  
GO  

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

Использование OPENROWSET(BULK...) и файла форматирования в формате, отличном от XMLUsing OPENROWSET(BULK...) and Non-XML Format File

Выполните следующий код Transact-SQL в Microsoft SQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.fmt'
        ) AS t1;
GO

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

Использование OPENROWSET(BULK...) и XML-файла форматированияUsing OPENROWSET(BULK...) and XML Format File

Выполните следующий код Transact-SQL в Microsoft SQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField 
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.xml'  
       ) AS t1;
GO

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

См. также:See Also

bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
Пропуск столбца таблицы с помощью файла форматирования (SQL Server) Use a Format File to Skip a Table Column (SQL Server)
Использование файла форматирования для сопоставления столбцов таблицы с полями файла данных (SQL Server)Use a Format File to Map Table Columns to Data-File Fields (SQL Server)