Поделиться через

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

Применимо к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureАналитика Azure SynapseАналитическая платформа System (PDW)

В этой статье показано использование файла форматирования в операциях массового импорта. Файл форматирования сопоставляет поля в файле данных столбцам таблицы. Дополнительные сведения см. в статье "Создание файла форматирования с помощью BCP(SQL Server").

Предпосылки

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

Примеры файлов форматирования в этой статье основаны на таблице и файле данных, которые определены ниже.

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

Приведенный ниже сценарий создает тестовую базу данных и таблицу с именем 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 вместо пути к файлу данных.
  • Для параметра форматирования также требуется параметр -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

Файл D:\BCP\myFirstImport.fmt, не в формате XML, должен выглядеть следующим образом:

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 (SQL Server ). Следующая команда будет использовать служебную программу bcp для создания XML-файла форматирования myFirstImport.xmlна основе схемы myFirstImport.

  • Чтобы использовать команду bcp для создания файла форматирования, укажите format аргумент и используйте nul вместо пути к файлу данных.
  • Для параметра формата всегда требуется -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 (SQL Server)

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

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 (SQL Server)

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

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

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

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

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

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

Дополнительные примеры

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