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

Статья
04/04/2023

Применимо к: SQL Server Azure SQL Database Управляемый экземпляр SQL Azure Azure Synapse Analytics Analytics Platform System (PDW)

Количество полей в файле данных может превышать количество столбцов в таблице. В этом подразделе описан процесс изменения файлов форматирования в форматах XML и не XML с целью сопоставления столбцов таблицы с полями файла данных и пропуска остальных полей. Ознакомьтесь с разделом Создание файла форматирования (SQL Server) для получения дополнительных сведений.

Примечание

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

Примечание

Этот синтаксис, включая массовую вставку, не поддерживается в Azure Synapse Analytics. В Azure Synapse Analytics и других платформах облачных баз данных перемещайте данные с помощью инструкции COPY в Фабрика данных Azure или с помощью инструкций T-SQL, таких как COPY INTO и PolyBase.

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

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

Пример таблицы

Приведенный ниже сценарий создает тестовую базу данных и таблицу с именем myTestSkipField. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

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

Пример файла данных

Создайте пустой файл D:\BCP\myTestSkipField.bcp и вставьте следующие данные:

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

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

Для массового импорта из файла myTestSkipField.bcp в таблицу myTestSkipField файл форматирования должен сделать следующий код:

сопоставить первое поле данных с первым столбцом, PersonID;
пропустить второе поле данных;
сопоставить третье поле данных со вторым столбцом, FirstName;
сопоставить четвертое поле данных с третьим столбцом, LastName.

Простейший способ создать файл форматирования заключается в использовании служебной программы bcp. Во-первых, создайте базовый файл форматирования из существующей таблицы. Во-вторых, измените его в соответствии с фактическим файлом данных.

Создание файла форматирования, отличного от XML

Подробные сведения см. в статье Файлы форматирования, отличные от XML (SQL Server). Следующая команда будет использовать служебную программу bcp для создания файла форматирования myTestSkipField.fmtв формате, отличном от XML, на основе схемы myTestSkipField. Кроме того, квалификатор c используется для указания символьных данных, t, используется для указания запятую в качестве признака конца поля, а T используется для указания доверенного соединения с использованием встроенной системы безопасности. В командной строке введите следующую команду:

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

Изменение файла форматирования, отличного от XML

Изучите структуру файлов форматирования, отличных от XML, на наличие терминологии. Откройте D:\BCP\myTestSkipField.fmt в Блокноте и внесите следующие изменения:

Скопируйте всю строку для FirstName из файла форматирования и вставьте ее на следующей строке после FirstName .
Увеличьте значения порядка полей файла узлов на единицу для новой строки и всех последующих строк.
Увеличьте значение числа столбцов в соответствии с действительным числом полей в файле данных.
Измените порядок столбцов сервера с 2 на 0 во второй строке файла форматирования.

Сравните внесенные изменения:

До:

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

После:

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

Теперь измененный файл форматирования отображает:

4 поля данных
Первое поле данных в myTestSkipField.bcp сопоставляется с первым столбцом. myTestSkipField.. PersonID
Второе поле данных в myTestSkipField.bcp не сопоставляется ни с одним из столбцов.
Третье поле данных в myTestSkipField.bcp сопоставляется со вторым столбцом. myTestSkipField.. FirstName
Четвертое поле данных в myTestSkipField.bcp сопоставляется с третьим столбцом. myTestSkipField.. LastName

Создание XML-файла форматирования

Подробные сведения см. в статье Xml Format Files (SQL Server). Следующая команда будет использовать служебную программу bcp для создания XML-файла форматирования myTestSkipField.xmlна основе схемы myTestSkipField. Кроме того, квалификатор c используется для указания символьных данных, t, используется для указания запятую в качестве признака конца поля, а T используется для указания доверенного соединения с использованием встроенной системы безопасности. Для создания XML-файла форматирования должен использоваться квалификатор x . В командной строке введите следующую команду:

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

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

Ознакомьтесь с синтаксисом схемы для XML-файлов форматирования , чтобы ознакомиться с терминологией. Откройте D:\BCP\myTestSkipField.xml в Блокноте и внесите следующие изменения:

Скопируйте все второе поле и вставьте его сразу после второго поля на следующей строке.
Увеличьте значение FIELD ID на 1 для нового поля и всех последующих полей.
Увеличьте значение COLUMN SOURCE на 1 для FirstNameи LastName в соответствии с исправленным сопоставлением.

Сравните внесенные изменения:

До:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://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>

После:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://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>

Теперь измененный файл форматирования отображает:

4 поля данных
Поле 1, соответствующее столбцу 1, сопоставляется с первым столбцом таблицы. myTestSkipField.. PersonID
Поле 2, не соответствующее ни одному из столбцов поэтому не сопоставляется ни с одним столбцом таблицы.
Поле 3, соответствующее столбцу 3, сопоставляется со вторым столбцом таблицы. myTestSkipField.. FirstName
Поле 4, соответствующее столбцу 4, сопоставляется с третьим столбцом таблицы. myTestSkipField.. LastName

Импорт данных с помощью файла форматирования для пропуска поля данных

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

Использование bcp и файла форматирования, отличного от XML

В командной строке введите следующую команду:

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

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

В командной строке введите следующую команду:

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

Использование инструкции BULK INSERT и файла форматирования, отличного от XML

Выполните следующий запрос Transact-SQL в 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-файла форматирования

Выполните следующий запрос Transact-SQL в 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...) и файла форматирования, отличного от XML

Выполните следующий запрос Transact-SQL в 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-файла форматирования

Выполните следующий запрос Transact-SQL в 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;

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

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

Пример таблицы

Пример файла данных

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

Создание файла форматирования, отличного от XML

Изменение файла форматирования, отличного от XML

Создание XML-файла форматирования

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

Импорт данных с помощью файла форматирования для пропуска поля данных

Использование bcp и файла форматирования, отличного от XML

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

Использование инструкции BULK INSERT и файла форматирования, отличного от XML

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

Использование OPENROWSET(BULK...) и файла форматирования, отличного от XML

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

Дальнейшие действия

Обратная связь

Обратная связь

Дополнительные ресурсы