Massenimport von Daten mithilfe einer Formatdatei (SQL Server)Use a Format File to Bulk Import Data (SQL Server)

DIESES THEMA GILT FÜR: JaSQL ServerJaAzure SQL-DatenbankJaAzure SQL Data Warehouse JaParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

In diesem Thema wird die Verwendung einer Formatdatei bei Massenimportvorgängen beschrieben.This topic illustrates the use of a format file in bulk-import operations. Eine Formatdatei ordnet die Felder der Datendatei den Spalten einer Tabelle zu.A format file maps the fields of the data file to the columns of the table. Weitere Informationen finden Sie unter Erstellen einer Formatdatei (SQL Server) .Please review Create a Format File (SQL Server) for additional information.

OutlineOutline
VorbereitungenBefore You Begin
BeispieltestbedingungenExample Test Conditions
 ● Beispieltabelle ● Sample Table
 ● Beispieldatendatei ● Sample Data File
Erstellen der FormatdateienCreating the Format Files
 ● Erstellen einer Nicht-XML-Formatdatei ● Creating a Non-XML Format File
 ● Erstellen einer XML-Formatdatei ● Creating an XML Format File
Verwenden einer Formatdatei für einen Massenimport von DatenUsing a Format File to Bulk Import Data
 ● Verwenden von BCP- und der Nicht-XML-Formatdatei ● Using bcp and Non-XML Format File
 ● Verwenden von BCP und der XML-Formatdatei ● Using bcp and XML Format File
 ● Verwenden von BULK INSERT und der Nicht-XML-Formatdatei ● Using BULK INSERT and Non-XML Format File
 ● Verwenden von BULK INSERT und der XML-Formatdatei ● Using BULK INSERT and XML Format File
 ● Verwenden von OPENROWSET(BULK...) und der Nicht-XML-Formatdatei ● Using OPENROWSET(BULK...) and Non-XML Format File
 ● Verwenden von OPENROWSET(BULK...) und der XML-Formatdatei ● Using OPENROWSET(BULK...) and XML Format File

VorbereitungenBefore You Begin

  • Damit eine Formatdatei mit einer Datendatei mit Unicode-Zeichen verwendet werden kann, müssen alle Eingabefelder Unicode-Textzeichenfolgen enthalten (d.h., entweder Unicode-Zeichenfolgen einer festen Länge oder Unicode-Zeichenfolgen mit Abschlusszeichen).For a format file to work with a Unicode character data file, all input fields must be Unicode text strings (that is, either fixed-size or character-terminated Unicode strings).
  • Verwenden Sie in der Formatdatei einen der folgenden Datentypen für den Massenexport oder -import von SQLXML -Daten:To bulk export or import SQLXML data, use one of the following data types in your format file:
    • SQLCHAR or SQLVARYCHAR (die Daten werden in der Clientcodepage gesendet bzw. in der durch die Sortierung implizierten Codeseite)SQLCHAR or SQLVARYCHAR (the data is sent in the client code page or in the code page implied by the collation)
    • SQLNCHAR oder SQLNVARCHAR (die Daten werden als Unicode gesendet)SQLNCHAR or SQLNVARCHAR (the data is sent as Unicode)
    • SQLBINARY or SQLVARYBIN (die Daten werden ohne Konvertierung gesendet)SQLBINARY or SQLVARYBIN (the data is sent without any conversion).
  • Azure SQL-Datenbank und Azure SQL Data Warehouse unterstützen nur das Hilfsprogramm bcp.Azure SQL Database and Azure SQL Data Warehouse only support bcp. Weitere Informationen finden Sie unter:For additional information, see:

BeispieltestbedingungenExample Test Conditions

Die in diesem Thema enthaltenen Beispiele für Formatdateien basieren auf der Tabelle und der Datendatei, die nachstehend definiert sind.The examples of format files in this topic are based on the table and data file defined below.

BeispieltabelleSample Table

Das folgende Skript erstellt eine Testdatenbank und eine Tabelle namens myFirstImport.The script below creates a test database and a table named myFirstImport. Führen Sie den folgenden Transact-SQL-Befehl in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.MyFirstImport (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );

BeispieldatendateiSample Data File

Erstellen Sie mit Editor die leere Datei D:\BCP\myFirstImport.bcp , und fügen Sie die folgenden Daten ein:Using Notepad, create an empty file D:\BCP\myFirstImport.bcp and insert the following data:

1,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
3,Stella,Rosenhain,1992-03-02

Alternativ können Sie das folgende PowerShell-Skript ausführen, um die Datei zu erstellen und aufzufüllen:Alternatively, you can execute the following PowerShell script to create and populate the data file:

cls
# 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;
Invoke-Item $bcpFile;

Erstellen der FormatdateienCreating the Format Files

SQL Server unterstützt zwei Typen von Formatdateien: Nicht-XML- und XML-Format.SQL Server support two types of format file: non-XML format and XML format. Nicht-XML ist das ursprüngliche Format, das von früheren Versionen von SQL Server unterstützt wird.The non-XML format is the original format that is supported by earlier versions of SQL Server.

Erstellen einer Nicht-XML-FormatdateiCreating a Non-XML Format File

Ausführliche Informationen finden Sie unter Nicht-XML-Formatdateien (SQL Server) .Please review Non-XML Format Files (SQL Server) for detailed information. Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die Nicht-XML-Formatdatei myFirstImport.fmtzu erstellen, die auf dem Schema von myFirstImportbasiert.The following command will use the bcp utility to generate a non-xml format file, myFirstImport.fmt, based on the schema of myFirstImport. Geben Sie das format -Argument an, um einen bcp-Befehl zum Erstellen einer Formatdatei zu verwenden, und verwenden Sie nul anstatt eines Datendateipfads.To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. Die Option „format“ erfordert außerdem die Option -f .The format option also requires the -f option. Zusätzlich werden folgende Qualifizierer verwendet: c , um Zeichendaten anzugeben, t , um ein Komma als Feldabschlusszeichenanzugeben, und T , um eine vertrauenswürdige Verbindung anzugeben, für die integrierte Sicherheit verwendet wird.In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

bcp TestDatabase.dbo.myFirstImport format nul -c -f D:\BCP\myFirstImport.fmt -t, -T

REM Review file
Notepad D:\BCP\myFirstImport.fmt

Ihre Nicht-XML-Formatdatei D:\BCP\myFirstImport.fmt sollte wie folgt aussehen:Your non-XML format file, D:\BCP\myFirstImport.fmt should look as follows:

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              ""

Wichtig

Stellen Sie sicher, dass Ihre Nicht-XML-Formatdatei mit einem Wagenrücklauf/Zeilenvorschub endet.Ensure your non-XML format file ends with a carriage return\line feed. Andernfalls wird Ihnen möglicherweise die folgende Fehlermeldung angezeigt:Otherwise you will likely receive the following error message:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

Erstellen einer XML-FormatdateiCreating an XML Format File

Ausführliche Informationen finden Sie unter XML-Formatdateien (SQL Server) .Please review XML Format Files (SQL Server) for detailed information. Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die XML-Formatdatei myFirstImport.xmlzu erstellen, die auf dem Schema von myFirstImportbasiert.The following command will use the bcp utility to create an xml format file, myFirstImport.xml, based on the schema of myFirstImport. Geben Sie das format -Argument an, um einen bcp-Befehl zum Erstellen einer Formatdatei zu verwenden, und verwenden Sie nul anstatt eines Datendateipfads.To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. Für die Formatoption ist immer auch die Option -f erforderlich. Zum Erstellen einer XML-Formatdatei muss zudem die Option -x angegeben werden.The format option always requires the -f option, and to create an XML format file, you must also specify the -x option. Zusätzlich werden folgende Qualifizierer verwendet: c , um Zeichendaten anzugeben, t , um ein Komma als Feldabschlusszeichenanzugeben, und T , um eine vertrauenswürdige Verbindung anzugeben, für die integrierte Sicherheit verwendet wird.In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

bcp TestDatabase.dbo.myFirstImport format nul -c -x -f D:\BCP\myFirstImport.xml -t, -T

REM Review file
Notepad D:\BCP\myFirstImport.xml

Ihre XML-Formatdatei D:\BCP\myFirstImport.xml sollte wie folgt aussehen:Your XML format file, D:\BCP\myFirstImport.xml should look as follows:

\<?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="SQLVARYCHAR"/>
  \<COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  \<COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
 </ROW>
</BCPFORMAT>

Verwenden einer Formatdatei für einen Massenimport von DatenUsing a Format File to Bulk Import Data

Die nachstehenden Beispiele verwenden die oben erstellte Datenbank, Datendatei und Formatdatei.The examples below use the database, datafile, and format files created above.

Verwenden von bcp und der Nicht-XML-FormatdateiUsing bcp and Non-XML Format File

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

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"

Verwenden von bcp und der XML-FormatdateiUsing bcp and XML Format File

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

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;"

Verwenden von BULK INSERT und der Nicht-XML-FormatdateiUsing BULK INSERT and Non-XML Format File

Führen Sie den folgenden Transact-SQL-Befehl in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Verwenden von BULK INSERT und der XML-FormatdateiUsing BULK INSERT and XML Format File

Führen Sie den folgenden Transact-SQL-Befehl in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Verwenden von OPENROWSET(BULK...) und der Nicht-XML-FormatdateiUsing OPENROWSET(BULK...) and Non-XML Format File

Führen Sie den folgenden Transact-SQL-Befehl in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Verwenden von OPENROWSET(BULK...) und der XML-FormatdateiUsing OPENROWSET(BULK...) and XML Format File

Führen Sie den folgenden Transact-SQL-Befehl in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Weitere Beispiele.More examples!

Erstellen einer Formatdatei (SQL Server)Create a Format File (SQL Server)
Überspringen einer Tabellenspalte mithilfe einer Formatdatei (SQL Server)Use a Format File to Skip a Table Column (SQL Server)
Auslassen eines Datenfelds mithilfe einer Formatdatei (SQL Server)Use a Format File to Skip a Data Field (SQL Server)
Verwenden einer Formatdatei zum Zuordnen von Tabellenspalten zu Datendateifeldern (SQL Server)Use a Format File to Map Table Columns to Data-File Fields (SQL Server)

Siehe auchSee also

bcp (Hilfsprogramm) bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
Nicht-XML-Formatdateien (SQL Server) Non-XML Format Files (SQL Server)
XML-Formatdateien (SQL Server)XML Format Files (SQL Server)
Formatdateien zum Importieren oder Exportieren von Daten (SQL Server)Format Files for Importing or Exporting Data (SQL Server)