Formatdateien zum Importieren oder Exportieren von Daten (SQL Server)

Artikel
06/25/2013

Beim Massenimportieren bzw. -exportieren von Daten in eine bzw. aus einer SQL Server-Tabelle können Sie eine Formatdatei verwenden, um die 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 2012 unterstützt zwei Typen von Formatdateien: XML-Formate 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 sie im Vergleich zu Nicht-XML-Formatdateien mehrere Vorteile bieten. Weitere Informationen finden Sie unter XML-Formatdateien (SQL Server).

In diesem Thema:

Vorteile von Formatdateien
Beispiele für Formatdateien
Wann ist eine Formatdatei erforderlich?
Verwandte Aufgaben

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 AdventureWorks2012 -Beispieldatenbank. Diese Tabelle enthält vier Spalten: EmployeeID, Name, Title und ModifiedDate.

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

A.Verwenden einer Nicht-XML-Formatdatei

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

bcp AdventureWorks.HumanResources.myTeam format nul -f myTeam.Fmt -n -T 
The contents of this format file are as follows: 9.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 Nicht-XML-Formatdateien (SQL Server).

[Nach oben]

B.Verwenden einer XML-Formatdatei

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

bcp AdventureWorks.HumanResources.myTeam format nul -f myTeam.Xml -x -n -T

Die Formatdatei enthält Folgendes:

 <?xml version="1.0"?>
<BCPFORMAT xmlns="https://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).

[Nach oben]

Wann ist eine Formatdatei erforderlich?

Eine INSERT ... SELECT * FROM OPENROWSET(BULK...)-Anweisung erfordert stets eine Formatdatei.

Für bcp oder BULK INSERT ist in einfachen Situationen das Verwenden einer Formatdatei optional und selten notwendig. In komplexen Massenimportsituationen ist jedoch oft eine Formatdatei erforderlich.

Formatdateien sind in folgenden Fällen erforderlich:

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
Wenn keine Formatdatei vorhanden und für einen bcp-Befehl ein Datenformatschalter angegeben ist (-n, -c, -w oder -N) oder für einen BULK INSERT-Vorgang die Option DATAFILETYPE angegeben ist, wird das angegebene Datenformat als Standardmethode zum Interpretieren der Felder der Datendatei verwendet.