Einführung in Formatdateien
Eine Datei, in der Formatinformationen für die einzelnen Felder einer Datendatei in Bezug auf eine bestimmte Tabelle gespeichert werden, wird als Formatdatei bezeichnet. Mit einer Formatdatei werden alle Formatinformationen bereitgestellt, die für den Massenexport oder Massenimport von Daten benötigt werden. Eine Formatdatei stellt ein flexibles System für das Schreiben von Datendateien bereit, bei denen nur geringfügige oder keine Bearbeitung erforderlich ist, um sie mit anderen Datenformaten kompatibel zu machen, oder für das Lesen von Datendateien aus anderen Softwareprogrammen.
In MicrosoftSQL Server 2000 und früheren Versionen wird der Massenimport und -export mit einem einzigen Formatdateityp ausgeführt. Dies wird nach wie vor unterstützt. SQL Server 2005 und höhere Versionen unterstützen jedoch auch XML-Formatdateien als Alternative. Formatdateien vom ursprünglichen Typ werden als Nicht-XML-Formatdateien bezeichnet.
Alle Formatdateien enthalten Beschreibungen jedes Felds in einer Datendatei. XML-Formatdateien enthalten 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. Die XML-Formatdatei weist folgende Merkmale auf:
Selbsterklärend und einfach zu lesen, zu erstellen und zu erweitern.
Enthält die Datentypen von Zielspalten.
Dies ermöglicht die Trennung zwischen der Darstellungsweise von Daten in den Datendateien und der Zuordnung des betreffenden Datentyps für jedes Feld in der Datei. Wenn z. B. eine Datendatei eine Zeichendarstellung der Daten enthält, geht der entsprechende SQL-Spaltentyp verloren.
Der bcp-Befehl und die BULK INSERT-Anweisung verwenden die Zieltabellenspalten für die Typkonvertierung. Daher ist eine Zieltabelle erforderlich. Im Gegensatz dazu basiert die OPENROWSET(BULK...)-Funktion auf einer XML-Formatdatei zum Lesen von Daten aus einer Datendatei. Daher ist eine Zieltabelle hier optional.
Ermöglicht das Laden eines Felds aus einer Datendatei, das einen einzigen LOB-Datentyp (Large Object) enthält.
Die Syntax einer Formatdatei ist unabhängig von der Richtung des Vorgangs, die Syntax ist also für Massenexport und Massenimport identisch.
Informationen zum Layout der einzelnen Formatdateitypen finden Sie in den Beispielen weiter unten in diesem Thema.
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.
.gif "Hinweis") 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. |
Beispiele
Die folgenden Beispiele zeigen das Layout einer Nicht-XML-Formatdatei und einer XML-Formatdatei. Diese Formatdateien entsprechen der HumanResources.myTeam-Tabelle in der AdventureWorks-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 Erstellen der HumanResources.myTeam-Tabelle. |
A. Verwenden einer Formatdatei, die nicht das Format XML aufweist
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 Grundlegendes zu Nicht-XML-Formatdateien.
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 Grundlegendes zu XML-Formatdateien.