Использование файла форматирования для массового импорта данных (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)

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