• Artikel

Verwenden einer Formatdatei zum Zuordnen von Tabellenspalten für Datendateifelder

Eine Datendatei kann Felder in einer anderen Reihenfolge als die der entsprechenden Spalten in der Tabelle aufweisen. In diesem Thema werden Nicht-XML-Formatdateien und XML-Formatdateien dargestellt, die zum Anpassen an eine Datendatei, deren Felder eine andere Reihenfolge als die Tabellenspalten aufweisen, geändert wurden. Die geänderte Formatdatei ordnet die Datenfelder den entsprechenden Tabellenspalten zu.

HinweisHinweis

Mithilfe einer Nicht-XML- oder XML-Formatdatei kann eine Datendatei mithilfe eines bcp-Befehls, einer BULK INSERT-Anweisung oder einer INSERT ... SELECT * FROM OPENROWSET(BULK...)- Anweisung massenimportiert werden. Weitere Informationen finden Sie unter Verwenden einer Formatdatei für den Massenimport von Daten.

Beispieltabelle und Datendatei

Die in diesem Thema enthaltenen Beispiele über die geänderten Formatdateien basieren auf der folgenden Tabelle und Datendatei.

Beispieltabelle

Für die Beispiele in diesem Thema muss in der AdventureWorks-Beispieldatenbank unter dem dbo-Schema eine Tabelle namens myTestOrder erstellt werden. Führen Sie den folgenden Code aus, um diese Tabelle im SQL Server Management Studio-Abfrage-Editor zu erstellen:

USE AdventureWorks2008R2;
GO
CREATE TABLE myTestOrder 
   (
   Col1 smallint,
   Col2 nvarchar(50) ,
   Col3 nvarchar(50) , 
   Col4 nvarchar(50) 
   );
GO

Datendatei

Die Datendatei myTestOrder-c.txt enthält die folgenden Datensätze:

DataField3,DataField2,1,DataField4
DataField3,DataField2,1,DataField4
DataField3,DataField2,1,DataField4

Zum Massenimportieren der Daten von myTestSkipCol2-c.dat in die Tabelle myTestSkipCol muss in der Formatdatei das erste Datenfeld der Spalte Col3, das zweite Datenfeld der Spalte Col2, das dritte Datenfeld der Spalte Col1 und das vierte Datenfeld der Spalte Col4 zugeordnet werden.

Verwenden einer Nicht-XML-Formatdatei

Sie können die Reihenfolge einer Spaltenzuordnung ändern, indem Sie zur Positionsangabe des entsprechenden Datenfelds den Reihenfolgenwert der Spalte ändern.

Die folgende Beispielformatdatei im Nicht-XML-Format, myTestOrder.fmt, ordnet die Felder in myTestOrder-c.txt den Spalten der myTestOrder-Tabelle zu. Informationen zum Erstellen der Datendatei und der Tabelle finden Sie unter "Beispieltabelle und Datendatei" weiter oben in diesem Thema. Die Formatdatei verwendet das Zeichendatenformat.

Die Formatdatei enthält die folgenden Informationen:

9.0
4
1       SQLCHAR       0       100     ","     3     Col3               SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       100     ","     2     Col2               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       7       ","     1     Col1               ""
4       SQLCHAR       0       100     "\r\n"  4     Col4               SQL_Latin1_General_CP1_CI_AS
HinweisHinweis

Weitere Informationen zum Layout von Nicht-XML-Formatdateien finden Sie unter Grundlegendes zu Nicht-XML-Formatdateien.

Beispiel

Im folgenden Beispiel wird eine BULK INSERT-Anweisung verwendet, um mithilfe der Nicht-XML-Formatdatei myTestOrder.fmt einen Massenimport der Daten aus der Datendatei myTestOrder-c.txt in die myTestOrder-Beispieltabelle auszuführen.

Führen Sie im SQL Server Management Studio-Abfrage-Editor Folgendes aus:

USE AdventureWorks2008R2;
GO
BULK INSERT myTestOrder
FROM 'C:\myTestOrder-c.txt' 
WITH (formatfile='C:\myTestOrder.fmt');
GO

Verwenden einer XML-Formatdatei

Die folgende Beispielformatdatei im Nicht-XML-Format, myTestOrder.xml, ordnet den Spalten der myTestOrder-Tabelle die Felder in myTestOrder-c.txt zu. Informationen zum Erstellen der Datendatei und der Tabelle finden Sie weiter oben in diesem Thema unter "Beispieltabelle und Datendatei".

Die Formatdatei myTestOrder.xml enthält die folgenden Informationen:

<?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="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <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="7"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="3" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="1" NAME="Col3" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>
HinweisHinweis

Informationen über die Syntax eines XML-Schemas und weitere Beispiele von XML-Formatdateien finden Sie unter Grundlegendes zu XML-Formatdateien.

Beispiel

Im folgenden Beispiel wird der BULK-Rowsetanbieter OPENROWSET verwendet, um mithilfe der XML-Formatdatei myTestOrder.xml einen Datenimport aus der Datendatei myTestOrder-c.txt in die myTestOrder-Beispieltabelle auszuführen. Die INSERT… SELECT-Anweisung gibt die Spaltenliste in der Auswahlliste an.

Führen Sie im Abfrage-Editor von SQL Server Management Studio folgenden Code aus:

USE AdventureWorks2008R2;
GO
INSERT INTO myTestOrder 
  SELECT Col1, Col2, Col3, Col4
      FROM  OPENROWSET(BULK  'C:\myTestOrder-c.txt',
      FORMATFILE='C:\myTestOrder.Xml'  
       ) AS t1;
GO