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

Применимо к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureСлужба аналитики Azure SynapseПлатформа аналитики (PDW)

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

Дополнительные сведения см. в статье "Создание файла форматирования с помощью bcp (SQL Server)".

Примечание.

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

Примечание.

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

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

Примеры измененных файлов формата в этой статье основаны на примере таблицы myTestSkipField и файла D:\BCP\myTestSkipField.bcpданных. Измените расположение локального файла в примере кода на расположение файла на компьютере.

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

Скрипт создает тестовую базу данных и таблицу с именем 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 в Блокноте и внесите следующие изменения:

1. Скопируйте всю строку для FirstName из файла форматирования и вставьте ее на следующей строке после FirstName .
2. Увеличьте значение порядка в файле хоста на один для новой строки и всех последующих строк.
3. Увеличьте значение числа столбцов в соответствии с действительным числом полей в файле данных.
4. Измените порядок столбцов сервера с 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 в Блокноте и внесите следующие изменения:

1. Скопируйте все второе поле и вставьте его сразу после второго поля на следующей строке.
2. Увеличьте значение FIELD ID на 1 для новой FIELD и для каждого последующего FIELD.
3. 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 поля данных
• FIELD 1, который соответствует COLUMN 1, сопоставляется с первым столбцом таблицы. myTestSkipField.. PersonID
• FIELD 2 не соответствует ни одному COLUMN и, таким образом, не сопоставляется ни с одним столбцом таблицы.
• FIELD 3, которое соответствует COLUMN 3, сопоставляется со вторым столбцом таблицы. myTestSkipField.. FirstName
• FIELD 4, соответствующее COLUMN 4, сопоставляется с третьим столбцом таблицы. myTestSkipField.. LastName

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

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

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

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

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

Использование файлов формата bcp и XML (SQL Server)

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

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

Использование BULK INSERT (Transact-SQL) и файла форматирования, отличного от 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 (Transact-SQL) и XML-файлов форматирования (SQL Server)

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

Выполните следующий код 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;

Связанный контент

• Утилита bcp
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• Использование файла форматирования для пропуска столбца таблицы (SQL Server)
• Использование файла форматирования для сопоставления столбцов таблиц с полями файлов данных (SQL Server)