Использование файла форматирования для массового импорта данных (SQL Server)
Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)
В этой статье показано использование файла форматирования в операциях массового импорта. Файл форматирования сопоставляет поля в файле данных столбцам таблицы. Дополнительные сведения см. в статье Создание файла форматирования (SQL Server).
Перед началом
- Чтобы файл форматирования мог работать с файлом символьных данных в Юникоде, все поля ввода должны представлять собой текстовые строки Юникода (то есть строки Юникода фиксированной длины или заканчивающиеся символом конца строки).
- Чтобы массово экспортировать или импортировать SQLXML -данные, используйте один из следующих типов данных в файле форматирования:
- SQLCHAR или SQLVARCHAR (данные отправляются в кодовой странице клиента или кодовой странице, определенной параметрами сортировки);
- SQLNCHAR или SQLNVARCHAR (данные отправляются в Юникоде);
- SQLBINARY или SQLVARBIN (данные отправляются без преобразования).
- База данных SQL Azure и Azure Synapse Analytics поддерживают только bcp. Дополнительные сведения см. в следующих источниках:
- Этот синтаксис, включая массовую вставку, не поддерживается в Azure Synapse Analytics. В Azure Synapse Analytics и других платформах облачных баз данных перемещайте данные с помощью инструкции COPY в Фабрика данных Azure или с помощью инструкций T-SQL, таких как COPY INTO и PolyBase.
Пример условий теста
Примеры файлов форматирования в этой статье основаны на таблице и файле данных, которые определены ниже.
Пример таблицы
Приведенный ниже сценарий создает тестовую базу данных и таблицу с именем myFirstImport
. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management Studio (SSMS):
CREATE DATABASE TestDatabase;
GO
USE TestDatabase;
CREATE TABLE dbo.MyFirstImport (
PersonID smallint,
FirstName varchar(25),
LastName varchar(30),
BirthDate Date
);
Пример файла данных
С помощью Блокнота создайте пустой файл D:\BCP\myFirstImport.bcp
и вставьте следующие данные:
1,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
3,Stella,Rosenhain,1992-03-02
Кроме того, можно выполнить следующий сценарий PowerShell для создания и заполнения файла данных:
Clear-Host
# revise directory as desired
$dir = 'D:\BCP\';
$bcpFile = Join-Path -Path $dir -ChildPath 'MyFirstImport.bcp';
# Confirm directory exists
IF ((Test-Path -Path $dir) -eq 0)
{
Write-Host "The path $dir does not exist; please create or modify the directory.";
RETURN;
};
# Clear content, will error if file does not exist, can be ignored
Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;
# Add data
Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,1992-03-02';
# Review content
Get-Content -Path $bcpFile;
Notepad.exe $bcpfile;
Создание файлов форматирования
SQL Server поддерживает два типа файлов форматирования: файлы форматирования в формате, отличном от XML, и XML-файлы форматирования. Файл форматирования не в формате XML поддерживается более ранними версиями SQL Server.
Создание файла форматирования, отличного от XML
Подробные сведения см. в статье Файлы форматирования, отличные от XML (SQL Server). Следующая команда будет использовать служебную программу bcp для создания файла форматирования myFirstImport.fmt
в формате, отличном от XML, на основе схемы myFirstImport
. Чтобы создать файл форматирования при помощи служебной программы bcp, укажите аргумент format , а вместо пути файла данных задайте значение nul . Параметр format также требует наличия параметра -f . Кроме того, в этом примере квалификатор c используется для указания символьных данных, t, используется для указания запятой в качестве признака конца поля, а T используется для указания доверенного подключения с использованием встроенной системы безопасности. В командной строке введите следующую команду:
bcp TestDatabase.dbo.myFirstImport format nul -c -f D:\BCP\myFirstImport.fmt -t, -T
REM Review file
Notepad D:\BCP\myFirstImport.fmt
Файл форматирования не в формате XML, D:\BCP\myFirstImport.fmt
, должен выглядеть следующим образом:
13.0
4
1 SQLCHAR 0 7 "," 1 PersonID ""
2 SQLCHAR 0 25 "," 2 FirstName SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 30 "," 3 LastName SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 11 "\r\n" 4 BirthDate ""
Важно!
Убедитесь, что файл форматирования не в формате XML заканчивается символом перевода строки или возврата каретки. В противном случае, скорее всего, появится следующее сообщение об ошибке:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file
Создание XML-файла форматирования
Подробные сведения см. в статье Xml Format Files (SQL Server). Следующая команда будет использовать служебную программу bcp для создания XML-файла форматирования myFirstImport.xml
на основе схемы myFirstImport
. Чтобы создать файл форматирования при помощи служебной программы bcp, укажите аргумент format , а вместо пути файла данных задайте значение nul . Параметр format всегда требует наличия параметра -f , а чтобы создать XML-файл форматирования, необходимо также задать параметр -x . Кроме того, в этом примере квалификатор c используется для указания символьных данных, t, используется для указания запятой в качестве признака конца поля, а T используется для указания доверенного подключения с использованием встроенной системы безопасности. В командной строке введите следующую команду:
bcp TestDatabase.dbo.myFirstImport format nul -c -x -f D:\BCP\myFirstImport.xml -t, -T
REM Review file
Notepad D:\BCP\myFirstImport.xml
Файл форматирования в формате XML, D:\BCP\myFirstImport.xml
, должен выглядеть следующим образом:
<?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="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="11"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
<COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARCHAR"/>
<COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARCHAR"/>
<COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
</ROW>
</BCPFORMAT>
Использование файла форматирования для массового импорта данных
В приведенных ниже примерах используется база данных, файл данных и файлы форматирования, созданные ранее.
Использование bcp и файла форматирования, отличного от XML
В командной строке введите следующую команду:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"
REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.fmt -T
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport"
Использование bcp и XML-файла форматирования
В командной строке введите следующую команду:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"
REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.xml -T
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport;"
Использование инструкции BULK INSERT и файла форматирования в формате, отличном от XML
Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport
FROM 'D:\BCP\myFirstImport.bcp'
WITH (FORMATFILE = 'D:\BCP\myFirstImport.fmt');
GO
-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;
Использование ИНСТРУКЦИИ BULK INSERT и XML-файла форматирования
Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport
FROM 'D:\BCP\myFirstImport.bcp'
WITH (FORMATFILE = 'D:\BCP\myFirstImport.xml');
GO
-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;
Использование OPENROWSET(BULK...) и файла форматирования, отличного от XML
Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myFirstImport.bcp',
FORMATFILE = 'D:\BCP\myFirstImport.fmt'
) AS t1;
GO
-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;
Использование OPENROWSET(BULK...) и XML-файла форматирования
Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myFirstImport.bcp',
FORMATFILE = 'D:\BCP\myFirstImport.xml'
) AS t1;
GO
-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;
Дополнительные примеры
Создание файла форматирования (SQL Server)
Пропуск столбца таблицы с помощью файла форматирования (SQL Server)
Использование файла форматирования для пропуска поля данных (SQL Server)
Использование файла форматирования для сопоставления столбцов таблицы с полями файла данных (SQL Server)
Дальнейшие действия
Обратная связь
https://aka.ms/ContentUserFeedback.
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделеОтправить и просмотреть отзыв по