Formatdateien zum Importieren oder Exportieren von Daten (SQL Server)

  • Artikel

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAnalytics Platform System (PDW)

Beim Massenimportieren bzw. -exportieren von Daten in eine bzw. aus einer SQL Server-Tabelle können Sie eine Formatdatei verwenden, um alle für den Massenimport oder -export erforderlichen Informationen zu speichern. Dazu zählen Formatinformationen für jedes Feld einer Datendatei in Bezug auf die betreffende Tabelle.

SQL Server unterstützt zwei Typen von Formatdateien: XML- und Nicht-XML-Formatdateien. Sowohl Nicht-XML-Formatdateien als auch XML-Formatdateien enthalten Beschreibungen jedes Felds in einer Datendatei. XML-Formatdateien enthalten darüber hinaus auch Beschreibungen der entsprechenden Tabellenspalten. Im Allgemeinen sind XML-Formatdateien und Nicht-XML-Formatdateien austauschbar. Es empfiehlt sich jedoch, für neue Formatdateien die XML-Syntax zu verwenden, weil sich im Vergleich zu Nicht-XML-Formatdateien mehrere Vorteile ergeben. Weitere Informationen finden Sie unter XML-Formatdateien (SQL Server).

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.

Vorteile von Formatdateien

Formatdateien stellen ein flexibles System für das Schreiben von Datendateien bereit, die für die Kompatibilität mit anderen Datenformaten und zum Lesen von Datendateien aus anderen Softwareprogrammen nur geringfügig oder gar nicht bearbeitet werden müssen.

Durch Verwenden einer Formatdatei ist es möglich, Daten per Massenimport zu kopieren, ohne überflüssige Daten hinzufügen oder löschen oder vorhandene Daten in der Datendatei neu anordnen zu müssen. Formatdateien sind besonders hilfreich, wenn Felder in der Datendatei und Spalten in der Tabelle nicht übereinstimmen.

Beispiele für Formatdateien

Die folgenden Beispiele zeigen das Layout einer Nicht-XML-Formatdatei und einer XML-Formatdatei. Diese Formatdateien entsprechen der HumanResources.myTeam -Tabelle in der AdventureWorks2022 -Beispieldatenbank. Diese Tabelle enthält vier Spalten: EmployeeID, Name, Titleund ModifiedDate.

Hinweis

Informationen zu dieser Tabelle und zum Erstellen derselben finden Sie unter HumanResources.myTeam-Beispieltabelle (SQL Server).

.A Verwenden einer Nicht-XML-Formatdatei

Die folgende Nicht-XML-Formatdatei verwendet das native SQL Server-Datenformat für die HumanResources.myTeam-Tabelle. Diese Formatdatei wurde mithilfe des folgenden bcp -Befehls erstellt.

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.Fmt -n -T

Der bcp-Befehl bezieht sich standardmäßig auf eine lokale Standardinstanz von SQL Server mit Windows-Authentifizierung. Sie können wie gewünscht weitere Instanzen- und Anmeldeinformationen angeben. Weitere Informationen finden Sie unter bcp-Hilfsprogramm. Verwenden Sie z. B. zum Angeben einer benannten Remoteserverinstanz mit Windows-Authentifizierung Folgendes:

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.Fmt -n -T -S servername/instancename

Der Inhalt dieser Formatdatei lautet wie folgt, beginnend mit der Hauptversionsnummer von SQL Server und den Tabellenmetadaten-Informationen.

14.0
4
1       SQLSMALLINT   0       2       ""   1     EmployeeID               ""
2       SQLNCHAR      2       100     ""   2     Name                     SQL_Latin1_General_CP1_CI_AS
3       SQLNCHAR      2       100     ""   3     Title                    SQL_Latin1_General_CP1_CI_AS
4       SQLNCHAR      2       100     ""   4     Background               SQL_Latin1_General_CP1_CI_AS

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

B. Verwenden einer XML-Formatdatei

Die folgende XML-Formatdatei verwendet das native SQL Server-Datenformat für die HumanResources.myTeam-Tabelle. Diese Formatdatei wurde mithilfe des folgenden bcp -Befehls erstellt.

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

Die Formatdatei enthält Folgendes:

<?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="NativePrefix" LENGTH="1"/>
  <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="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="EmployeeID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Title" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Background" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>

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

Wann ist eine Formatdatei erforderlich?

Formatdateien sind in der Regel unter folgenden Umständen erforderlich:

  • Bei Verwenden einer INSERT ... SELECT * FROM OPENROWSET(BULK...) Anweisung.

  • Bei komplexen Massenimportsituationen mit bcp oder BULK INSERT.

  • Dieselbe Datendatei wird als Quelle für mehrere Tabellen mit unterschiedlichen Schemas verwendet.

  • Die Datendatei hat eine andere Anzahl von Feldern, als die Zieltabelle Spalten hat. Beispiel:

    • Die Zieltabelle enthält mindestens eine Spalte, für die entweder ein Standardwert definiert oder NULL zulässig ist.
    • Den Benutzern fehlen für mindestens eine Spalte in der Tabelle die SELECT/INSERT-Berechtigungen.
    • Eine einzige Datendatei wird für mindestens zwei Tabellen mit unterschiedlichen Schemas verwendet.

  • Die Spaltenreihenfolge ist bei der Datendatei und der Tabelle unterschiedlich.

  • Die abschließenden Zeichen oder Präfixlängen weichen bei den Spalten der Datendatei ab.

Hinweis

Ist keine Formatdatei vorhanden und ein bcp-Befehl gibt einen Datenformatschalter (-n, -c, -w oder -N) an oder eine BULK INSERT Operation gibt die DATAFILETYPE Option an, wird das angegebene Datenformat als Standardmethode zur Interpretation der Felder der Datendatei verwendet.

Zugehöriger Inhalt