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

Статья
07/04/2024

Применимо: SQL Server База данных SQL Azure Управляемый экземпляр 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-файлах форматирования (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-файла форматирования

Следующие шаги

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

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