Auslassen eines Datenfelds mithilfe einer Formatdatei (SQL Server)

Artikel
06/25/2013

Eine Datendatei kann mehr Felder enthalten, als Spalten in der Tabelle vorhanden sind. In diesem Thema wird beschrieben, wie Nicht-XML- und XML-Formatdateien an eine Datendatei mit mehr Feldern angepasst werden können, indem die Tabellenspalten den entsprechenden Datenfeldern zugeordnet und die übrigen Felder ignoriert werden.

Hinweis
Mit 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).

Beispiel für Datendatei und Tabelle

Die Beispiele für geänderte Formatdateien in diesem Thema basieren auf der folgenden Tabelle und Datendatei.

Beispieltabelle

Für die Beispiele muss in der AdventureWorks2012-Beispieldatenbank unter dem dbo-Schema eine Tabelle mit dem Namen myTestSkipField erstellt werden. Wenn Sie diese Tabelle erstellen möchten, führen Sie im Microsoft SQL Server Management Studio-Abfrage-Editor folgenden Code aus:

USE AdventureWorks2012;
GO
CREATE TABLE myTestSkipField 
   (
   PersonID smallint,
   FirstName nvarchar(50) ,
   LastName nvarchar(50) 
   );
GO

Beispieldatendatei

Die Datendatei myTestSkipField-c.dat enthält die folgenden Datensätze:

1,Skipme,DataField3,DataField4
1,Skipme,DataField3,DataField4
1,Skipme,DataField3,DataField4

Wenn ein Massenimport für Daten aus myTestSkipField-c.dat in die myTestSkipField-Tabelle ausgeführt werden soll, muss die Formatdatei folgende Funktionen ausführen:

Der ersten Spalte (PersonID) das erste Datenfeld zuordnen.
Das zweite Datenfeld auslassen.
Der zweiten Spalte (FirstName) das dritte Datenfeld zuordnen.
Der dritten Spalte (LastName) das vierte Datenfeld zuordnen.

Nicht-XML-Formatdatei für weitere Datenfelder

Die Formatdatei myTestSkipField.fmt ordnet die Felder in myTestSkipField-c.dat den Spalten der myTestSkipField-Tabelle zu. Die Formatdatei verwendet das Zeichendatenformat. Damit eine Spaltenzuordnung ausgelassen werden kann, muss der Wert für die Spaltenreihenfolge auf 0 festgelegt werden, wie für die ExtraField-Spalte in der Formatdatei veranschaulicht.

Die Formatdatei myTestSkipField.fmt enthält die folgenden Informationen:

9.0
4
1       SQLCHAR       0       7       ","      1     PersonID               ""
2       SQLCHAR       0       100       ","    0     ExtraField             SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       100     "\r\n"   3     LastName               SQL_Latin1_General_CP1_CI_AS

Hinweis
Informationen zur Syntax von Nicht-XML-Formatdateien finden Sie unter Nicht-XML-Formatdateien (SQL Server).

Beispiele

Im folgenden Beispiel wird INSERT ... SELECT * FROM OPENROWSET(BULK...) mithilfe der Formatdatei myTestSkipField.fmt verwendet. Die Datendatei myTestSkipField-c.dat wird per Massenimport in die myTestSkipField-Tabelle übertragen. Hinweise zum Erstellen der Beispieltabelle und Datendatei finden Sie unter "Beispiel für Datendatei und Tabelle" in diesem Thema.

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

USE AdventureWorks2012;
GO
INSERT INTO myTestSkipField 
   SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestSkipField-c.dat',
      FORMATFILE='C:\myTestSkipField.fmt'  
       ) AS t1;
GO

XML-Formatdatei für weitere Datenfelder

Die in diesem Beispiel verwendete Formatdatei basiert auf einer anderen Formatdatei, myTestSkipField.xml, in der durchgängig das Zeichendatenformat verwendet wird und deren Felder der Anzahl und Reihenfolge der Spalten in der myTestSkipField-Tabelle entsprechen. Den Inhalt dieser Formatdatei können Sie sich unter Erstellen einer Formatdatei (SQL Server) ansehen.

Die Formatdatei myTestSkipField.xml ordnet die Felder in myTestSkipField-c.dat den Spalten der myTestSkipField-Tabelle zu. Die Formatdatei verwendet das Zeichendatenformat.

Die Formatdatei myTestSkipField.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="7"/>
  <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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="LastName" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Beispiele

Im folgenden Beispiel wird INSERT ... SELECT * FROM OPENROWSET(BULK...) mithilfe der Formatdatei myTestSkipField.Xml verwendet. Die Datendatei myTestSkipField-c.dat wird per Massenimport in die myTestSkipField-Tabelle übertragen. Hinweise zum Erstellen der Beispieltabelle und Datendatei finden Sie unter "Beispiel für Datendatei und Tabelle" in diesem Thema.

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

USE AdventureWorks2012;
GO
INSERT INTO myTestSkipField 
  SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestSkipField-c.dat',
      FORMATFILE='C:\myTestSkipField.xml'  
       ) AS t1;
GO