Использование файла форматирования для пропуска поля данных (SQL Server)
Применимо к:SQL Server
База данных SQL Azure
Управляемый экземпляр SQL Azure
Служба аналитики Azure Synapse
Платформа аналитики (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;