Erstellen einer Formatdatei mit bcp (SQL Server)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

In diesem Artikel wird die Verwendung des Hilfsprogramms bcp zum Erstellen einer Formatdatei für eine bestimmte Tabelle erläutert. Die Formatdatei basiert auf der angegebenen Datentypoption (-n, -c, -w, oder -N) sowie auf den Tabellen- bzw. Sichttrennzeichen.

Wenn Sie einen Massenimport in eine SQL Server-Tabelle oder einen Massenexport von Daten aus einer Tabelle ausführen, können Sie eine Formatdatei als flexibles System zum Schreiben von Datendateien verwenden. Formatdateien müssen zur Kompatibilität mit anderen Datenformaten und zum Lesen von Datendateien aus anderen Softwareprogrammen nur geringfügig oder gar nicht bearbeitet werden.

Begrenzungen

Die zum Lesen der Formatdatei verwendete Version des Hilfsprogramms bcp (bcp.exe) muss mit der Version, mit der die Formatdatei erstellt wurde, identisch oder eine höhere Version sein. Beispielsweise kann SQL Server 2016 (13.x) bcp eine Formatdatei der Version 12.0 lesen, die von SQL Server 2014 (12.x) bcp generiert wird, aber SQL Server 2014 (12.x) bcp kann keine Formatdatei der Version 13.0 lesen, die von SQL Server 2016 (13.x) bcp generiert wird.

Hinweis

Diese Syntax, einschließlich Masseneinfügen, wird in Azure Synapse Analytics nicht unterstützt. Führen Sie in Azure Synapse Analytics und anderen Clouddatenbank-Plattformintegrationen Datenverschiebung über die COPY-Anweisung in Azure Data Factory oder mithilfe von T-SQL-Anweisungen wie COPY INTO und PolyBase aus.

Erstellen von Formatdateien

SQL Server unterstützt zwei Typen von Formatdateien: Nicht-XML- und XML-Format. Nicht-XML ist das ursprüngliche Format, das von früheren Versionen von SQL Server unterstützt wird.

Im Allgemeinen sind XML-Formatdateien und Nicht-XML-Formatdateien austauschbar. Es empfiehlt sich jedoch, für Formatdateien XML-Syntax zu verwenden, weil sich im Vergleich zu Nicht-XML-Formatdateien mehrere Vorteile ergeben.

XML-Formatdatei

Dieser Artikel erfordert die AdventureWorks2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können. Adventure Works Cycles ist ein fiktionales Fertigungsunternehmen, das zum Demonstrieren von Datenbankkonzepten und -szenarien dient.

Erstellen Sie eine XML-Formatdatei

Geben Sie das format-Argument an, um einen bcp-Befehl zum Erstellen einer Formatdatei zu verwenden, und verwenden Sie nul anstatt eines Datendateipfads. Für die Option format ist immer auch die Option -f erforderlich. Zum Erstellen einer XML-Formatdatei muss zudem die Option -x angegeben werden, z. B. bcp <table_or_view> format nul -f <format_file_name> -x.

Um XML-Formatdateien klar zu kennzeichnen, empfiehlt es sich, als Suffix .xml zu verwenden, beispielsweise MyTable.xml.

Informationen zu Struktur und Feldern von XML-Formatdateien finden Sie unter XML-Formatdateien (SQL Server).

Beispiele

Dieser Abschnitt enthält die folgenden Beispiele, die die Verwendung der bcp-Befehle zum Erstellen von XML-Formatdateien erläutern. Die HumanResources.Department -Tabelle enthält vier Spalten: DepartmentID, Name, GroupNameund ModifiedDate.

A. Erstellen einer XML-Formatdatei für Zeichendaten

In diesem Beispiel wird eine XML-Formatdatei, Department.xml, für die HumanResources.Department -Tabelle erstellt. Die Formatdatei verwendet Zeichendatenformate und ein nicht standardmäßiges Feldabschlusszeichen (,). Der Inhalt der generierten Formatdatei wird nach dem Befehl angezeigt.

Der Befehl bcp enthält die folgenden Qualifizierer.

Qualifizierer	Beschreibung
format nul -x -f <format_file>	Gibt die XML-Formatdatei an.
-c	Gibt Zeichendaten an.
-t,	Gibt ein Komma (,) als Feldabschlusszeichen an. Hinweis: Wenn von der Datendatei das standardmäßige Feldabschlusszeichen (\t) verwendet wird, ist der -t-Schalter nicht erforderlich.
-T	Gibt an, dass das Hilfsprogramm bcp die Verbindung mit SQL Server mithilfe integrierter Sicherheit über eine vertrauenswürdige Verbindung herstellt. Wenn -T nicht angegeben wird, müssen Sie -U und -P angeben, um sich erfolgreich anzumelden.

Geben Sie an der Windows-Eingabeaufforderung den folgenden bcp -Befehl ein:

bcp AdventureWorks2022.HumanResources.Department format nul -c -x -f Department-c.xml -t, -T

Die generierte Formatdatei, Department-c.xml, enthält die folgenden XML-Elemente:

<?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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

Informationen zur Syntax dieser Formatdatei finden Sie unter Nicht-XML-Formatdateien (SQL Server). Informationen zu Zeichendaten finden Sie unter Verwenden des Zeichenformats zum Importieren oder Exportieren von Daten (SQL Server).

B. Erstellen einer XML-Formatdatei für native Daten

In diesem Beispiel wird eine XML-Formatdatei, Department-n.xml, für die HumanResources.Department -Tabelle erstellt. Die Formatdatei verwendet systemeigene Datentypen. Der Inhalt der generierten Formatdatei wird nach dem Befehl angezeigt.

Der Befehl bcp enthält die folgenden Qualifizierer.

Qualifizierer	Beschreibung
format nul -x -f <format_file>	Gibt die XML-Formatdatei an.
-n	Gibt systemeigene Datentypen an.
-T	Gibt an, dass das Hilfsprogramm bcp die Verbindung mit SQL Server mithilfe integrierter Sicherheit über eine vertrauenswürdige Verbindung herstellt. Wenn -T nicht angegeben wird, müssen Sie -U und -P angeben, um sich erfolgreich anzumelden.

Geben Sie an der Windows-Eingabeaufforderung den folgenden bcp -Befehl ein:

bcp AdventureWorks2022.HumanResources.Department format nul -x -f Department-n.xml -n -T

Die generierte Formatdatei, Department-n.xml, enthält die folgenden XML-Elemente:

<?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="NativeFixed" LENGTH="2"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NativeFixed" LENGTH="8"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

Informationen zur Syntax dieser Formatdatei finden Sie unter Nicht-XML-Formatdateien (SQL Server). Informationen zur Verwendung nativer Daten finden Sie unter Verwenden des nativen Formats zum Importieren oder Exportieren von Daten (SQL Server).

Nicht-XML-Formatdatei

Dieser Artikel erfordert die AdventureWorks2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können. Adventure Works Cycles ist ein fiktionales Fertigungsunternehmen, das zum Demonstrieren von Datenbankkonzepten und -szenarien dient.

Erstellen Sie eine Nicht-XML-Formatdatei

Geben Sie das format-Argument an, um einen bcp-Befehl zum Erstellen einer Formatdatei zu verwenden, und verwenden Sie nul anstatt eines Datendateipfads. Die Option format erfordert außerdem die Option -f, z. B.: bcp <table_or_view> format nul -f <format_file_name>.

Um Nicht-XML-Formatdateien klar zu kennzeichnen, empfiehlt es sich, als Suffix .fmt zu verwenden, beispielsweise MyTable.fmt.

Informationen zu Struktur und Feldern von Nicht-XML-Formatdateien finden Sie unter Verwenden von nicht-XML-Formatdateien (SQL Server).

Beispiele

Dieser Abschnitt enthält die folgenden Beispiele, die die Verwendung der bcp -Befehle zum Erstellen von Nicht-XML-Formatdateien erläutern. Die HumanResources.Department -Tabelle enthält vier Spalten: DepartmentID, Name, GroupNameund ModifiedDate.

A. Erstellen einer Nicht-XML-Formatdatei für native Daten

In diesem Beispiel wird eine XML-Formatdatei, Department-n.xml, für die HumanResources.Department -Tabelle erstellt. Die Formatdatei verwendet systemeigene Datentypen. Der Inhalt der generierten Formatdatei wird nach dem Befehl angezeigt.

Der Befehl bcp enthält die folgenden Qualifizierer.

Qualifizierer	Beschreibung
format nul -f <format_file>	Gibt die Nicht-XML-Formatdatei an.
-n	Gibt systemeigene Datentypen an.
-T	Gibt an, dass das Hilfsprogramm bcp die Verbindung mit SQL Server mithilfe integrierter Sicherheit über eine vertrauenswürdige Verbindung herstellt. Wenn -T nicht angegeben wird, müssen Sie -U und -P angeben, um sich erfolgreich anzumelden.

Geben Sie an der Windows-Eingabeaufforderung den folgenden bcp -Befehl ein:

bcp AdventureWorks2022.HumanResources.Department format nul -T -n -f Department-n.fmt

Die generierte Formatdatei Department-n.fmtenthält die folgenden Informationen:

12.0
4
1  SQLSMALLINT   0       2       ""   1     DepartmentID         ""
2  SQLNCHAR      2       100     ""   2     Name                 SQL_Latin1_General_CP1_CI_AS
3  SQLNCHAR      2       100     ""   3     GroupName            SQL_Latin1_General_CP1_CI_AS
4  SQLDATETIME   0       8       ""   4     ModifiedDate         ""

Weitere Informationen finden Sie unter Verwendung von Nicht-XML-Formatdateien (SQL Server).

B. Erstellen einer Nicht-XML-Formatdatei für Zeichendaten

In diesem Beispiel wird eine XML-Formatdatei, Department.fmt, für die HumanResources.Department -Tabelle erstellt. Die Formatdatei verwendet Zeichendatenformate und ein nicht standardmäßiges Feldabschlusszeichen (,). Der Inhalt der generierten Formatdatei wird nach dem Befehl angezeigt.

Der Befehl bcp enthält die folgenden Qualifizierer.

Qualifizierer	Beschreibung
format nul -f <format_file>	Gibt eine Nicht-XML-Formatdatei an.
-c	Gibt Zeichendaten an.
-T	Gibt an, dass das Hilfsprogramm bcp die Verbindung mit SQL Server mithilfe integrierter Sicherheit über eine vertrauenswürdige Verbindung herstellt. Wenn -T nicht angegeben wird, müssen Sie -U und -P angeben, um sich erfolgreich anzumelden.

Geben Sie an der Windows-Eingabeaufforderung den folgenden bcp -Befehl ein:

bcp AdventureWorks2022.HumanResources.Department format nul -c -f Department-c.fmt -T

Die generierte Formatdatei Department-c.fmtenthält die folgenden Informationen:

12.0
4
1  SQLCHAR       0       7       "\t"     1     DepartmentID            ""
2  SQLCHAR       0       100     "\t"     2     Name                    SQL_Latin1_General_CP1_CI_AS
3  SQLCHAR       0       100     "\t"     3     GroupName               SQL_Latin1_General_CP1_CI_AS
4  SQLCHAR       0       24      "\r\n"   4     ModifiedDate            ""

Weitere Informationen finden Sie unter Verwendung von Nicht-XML-Formatdateien (SQL Server).

C. Erstellen einer Nicht-XML-Formatdatei für native Unicode-Daten

Verwenden Sie zum Erstellen einer Nicht-XML-Formatdatei für systemeigene Unicode-Daten für die HumanResources.Department-Tabelle den folgenden Befehl:

bcp AdventureWorks2022.HumanResources.Department format nul -T -N -f Department-n.fmt

Weitere Informationen zur Verwendung von nativen Unicode-Daten finden Sie unter Verwenden des nativen Unicode-Formats zum Importieren oder Exportieren von Daten (SQL Server).

D: Erstellen einer Nicht-XML-Formatdatei für Unicode-Zeichendaten

Verwenden Sie zum Erstellen einer Nicht-XML-Formatdatei für Unicode-Zeichendaten für die HumanResources.Department-Tabelle den folgenden Befehl:

bcp AdventureWorks2022.HumanResources.Department format nul -T -w -f Department-w.fmt

Weitere Informationen zur Verwendung von Unicode-Zeichendaten finden Sie unter Verwenden des Unicode-Zeichenformats zum Importieren oder Exportieren von Daten (SQL Server).

F. Verwenden einer Formatdatei mit der Codeseitenoption

Wenn Sie eine Formatdatei mit bcp erstellen (d. h. mithilfe von bcp format), werden Informationen zur Sortierung/Codepage in die Formatdatei geschrieben.

Die folgende Beispielformatdatei, für eine Tabelle mit fünf Spalten, beinhaltet die Sortierung.

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0          Cyrillic_General_CS_AS
2  SQLCHAR         0       0       "\t"             2     c_1          Cyrillic_General_CS_AS
3  SQLCHAR         0       3000    "\t"             3     c_2          Cyrillic_General_CS_AS
4  SQLCHAR         0       5       "\t"             4     c_3          ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4          ""

Wenn Sie versuchen, Daten mithilfe von „bcp in -c -C65001 -f format_file“ oder „BULK INSERT/OPENROWSET ... FORMATFILE='format_file' CODEPAGE=65001“ nach SQL Server zu importieren, haben Informationen zur Sortierung/Codepage Priorität gegenüber der 65001-Option.

Wenn Sie daher eine Formatdatei generieren, müssen Sie die Sortierungsinformationen manuell aus der generierten Formatdatei löschen, bevor Sie die Daten in SQL Server zurück importieren.

Das folgende Beispiel führt die Formatdatei ohne Sortierungsinformationen auf.

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0              ""
2  SQLCHAR         0       0       "\t"             2     c_1              ""
3  SQLCHAR         0       3000    "\t"             3     c_2              ""
4  SQLCHAR         0       5       "\t"             4     c_3              ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4              ""

Zuordnen von Datenfeldern zu Tabellenspalten

In einer mit bcperstellten Formatdatei werden alle Tabellenspalten in ihrer Reihenfolge beschrieben. Sie können die Formatdatei ändern, um Tabellenzeilen neu anzuordnen oder auszulassen. Sie können Formatdateien an Datendateien anpassen, deren Felder nicht direkt Tabellenspalten zugeordnet werden können. Weitere Informationen finden Sie in den folgenden Artikeln:

• Überspringen einer Tabellenspalte mithilfe einer Formatdatei (SQL Server)
• Auslassen eines Datenfelds mithilfe einer Formatdatei (SQL Server)
• Verwenden einer Formatdatei zum Zuordnen von Tabellenspalten zu Datendateifeldern (SQL Server)

Zugehöriger Inhalt

• bcp (Hilfsprogramm)
• Nicht-XML-Formatdateien verwenden (SQL Server)
• XML-Formatdateien (SQL Server)