Freigeben über

Verwenden einer Formatdatei zum Zuordnen von Tabellenspalten zu Datendateifeldern (SQL Server)

  • Artikel

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 per Massenimport in eine Tabelle übertragen werden. Weitere Informationen finden Sie unter Massenimport von Daten mithilfe einer Formatdatei (SQL Server).

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 AdventureWorks2012-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 AdventureWorks2012;
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 Nicht-XML-Formatdateien (SQL Server).

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 AdventureWorks2012;
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 XML-Formatdateien (SQL Server).

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 AdventureWorks2012;
GO
INSERT INTO myTestOrder 
  SELECT Col1, Col2, Col3, Col4
      FROM  OPENROWSET(BULK  'C:\myTestOrder-c.txt',
      FORMATFILE='C:\myTestOrder.Xml'  
       ) AS t1;
GO

Siehe auch

Konzepte

Überspringen einer Tabellenspalte mithilfe einer Formatdatei (SQL Server)

Auslassen eines Datenfelds mithilfe einer Formatdatei (SQL Server)