Freigeben über

Verwenden einer Formatdatei zum Überspringen einer Tabellenspalte (SQL Server)

In diesem Thema werden Formatdateien beschrieben. Sie können eine Formatdatei verwenden, um den Import einer Tabellenspalte zu überspringen, wenn das Feld in der Datendatei nicht vorhanden ist. Eine Datendatei kann weniger Felder als die Anzahl der Spalten in der Tabelle enthalten, nur wenn die übersprungenen Spalten nullfähig sind und/oder einen Standardwert haben.

Beispieltabelle und Datendatei

In den folgenden Beispielen ist eine Tabelle erforderlich, die in der AdventureWorks2012-Beispieldatenbank unter dem dbo-Schema benannt myTestSkipCol ist. Erstellen Sie diese Tabelle wie folgt:

USE AdventureWorks2012;
GO
CREATE TABLE myTestSkipCol 
   (
   Col1 smallint,
   Col2 nvarchar(50) NULL,
   Col3 nvarchar(50) not NULL
   );
GO

In den folgenden Beispielen wird eine Beispieldatendatei verwendet, myTestSkipCol2.datdie nur zwei Felder enthält, obwohl die entsprechende Tabelle drei Spalten enthält:

1,DataForColumn3
1,DataForColumn3
1,DataForColumn3

Zum Massenimport von Daten aus myTestSkipCol2.dat in die myTestSkipCol Tabelle muss die Formatdatei das erste Datenfeld Col1 zuordnen, das zweite Feld Col3 und Col2 überspringen.

Verwenden einer Nicht-XML-Formatdatei

Sie können eine Nicht-XML-Formatdatei ändern, um eine Tabellenspalte zu überspringen. Dies umfasst in der Regel die Verwendung des bcp-Hilfsprogramms zum Erstellen einer standardmäßigen Nicht-XML-Formatdatei und zum Ändern der Standarddatei in einem Text-Editor. Die geänderte Formatdatei muss jedem vorhandenen Feld seine entsprechende Tabellenspalte zuordnen und angeben, welche Tabellenspalte oder Spalten übersprungen werden sollen. Es gibt zwei Alternativen zum Ändern einer standardmäßigen Nicht-XML-Datendatei. Alternativ wird angegeben, dass das Datenfeld in der Datendatei nicht vorhanden ist und dass keine Daten in die entsprechende Tabellenspalte eingefügt werden.

Erstellen einer Nicht-XML-Standardformatdatei

In diesem Thema wird die standardmäßige Nicht-XML-Formatdatei verwendet, die für die myTestSkipCol Beispieltabelle mithilfe des folgenden bcp-Befehls erstellt wurde:

bcp AdventureWorks2012..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T

Mit dem vorherigen Befehl wird eine Nicht-XML-Formatdatei erstellt. myTestSkipCol_Default.fmt Diese Formatdatei wird als Standardformatdatei bezeichnet, da sie das von bcp generierte Formular ist. In der Regel beschreibt eine Standardformatdatei eine 1:1-Entsprechung zwischen Datendateifeldern und Tabellenspalten.

Von Bedeutung

Möglicherweise müssen Sie den Namen der Serverinstanz angeben, mit der Sie eine Verbindung herstellen. Außerdem müssen Sie möglicherweise den Benutzernamen und das Kennwort angeben. Weitere Informationen finden Sie unter bcp Utility.

Die folgende Abbildung zeigt die Werte in diesen Beispielstandardformatdateien. Die Abbildung zeigt auch den Namen jedes Formatdateifelds.

Standardmäßige Nicht-XML-Formatdatei für myTestSkipCol

Hinweis

Weitere Informationen zu den Formatdateifeldern finden Sie unter Nicht-XML-Formatdateien (SQL Server).For more information about the format-file fields, see Non-XML Format Files (SQL Server).

Methoden zum Ändern einer Nicht-XML-Formatdatei

Um eine Tabellenspalte zu überspringen, bearbeiten Sie die standardmäßige Nicht-XML-Formatdatei, und ändern Sie die Datei mithilfe einer der folgenden alternativen Methoden:

  • Die bevorzugte Methode umfasst drei grundlegende Schritte. Löschen Sie zunächst eine beliebige Formatdateizeile, die ein Feld beschreibt, das in der Datendatei fehlt. Reduzieren Sie dann den Wert "Hostdateifeldreihenfolge" jeder Formatdateizeile, die einer gelöschten Zeile folgt. Das Ziel ist die sequenzielle "Hostdateifeldreihenfolge", 1 bis n, die die tatsächliche Position jedes Datenfelds in der Datendatei widerspiegeln. Reduzieren Sie schließlich den Wert im Feld "Anzahl der Spalten", um die tatsächliche Anzahl der Felder in der Datendatei widerzuspiegeln.

    Das folgende Beispiel basiert auf der Standardformatdatei für die myTestSkipCol Tabelle, die weiter oben in diesem Thema unter "Erstellen einer Nicht-XML-Standardformatdatei" erstellt wird. Diese geänderte Formatdatei ordnet das erste Datenfeld zu Col1, überspringt Col2und ordnet das zweite Datenfeld zu Col3. Die Zeile für Col2 wurde gelöscht. Andere Änderungen werden fett formatiert angezeigt:

    9.0
2
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

  • Alternativ können Sie zum Überspringen einer Tabellenspalte die Definition der Formatdatei-Zeile ändern, die der Tabellenspalte entspricht. In dieser Formatdateizeile müssen die Werte "Präfixlänge", "Länge der Hostdateidaten" und "Serverspaltenreihenfolge" auf 0 festgelegt werden. Außerdem müssen die Felder "Terminator" und "Spaltensortierung" auf "" (NULL) festgelegt werden.

    Der Wert "Serverspaltenname" erfordert eine nicht leere Zeichenfolge, obwohl der tatsächliche Spaltenname nicht erforderlich ist. Für die verbleibenden Formatfelder sind deren Standardwerte erforderlich.

    Das folgende Beispiel wird auch von der Standardformatdatei für die myTestSkipCol Tabelle abgeleitet. Werte, die 0 oder NULL sein müssen, werden fett formatiert angezeigt.

    9.0
3
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       00""0     Col2         ""
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Beispiele

Die folgenden Beispiele basieren auch auf der myTestSkipCol Beispieltabelle und der Beispieldatendatei, die myTestSkipCol2.dat in "Beispieltabelle und Datendatei" weiter oben in diesem Thema erstellt werden.

Verwenden von BULK INSERT

In diesem Beispiel wird eine der geänderten Nicht-XML-Formatdateien verwendet, die in "Methoden zum Ändern einer Nicht-XML-Formatdatei" weiter oben in diesem Thema erstellt wurden. In diesem Beispiel wird die Geänderte Formatdatei benannt C:\myTestSkipCol2.fmt. BULK INSERT Führen Sie zum Massenimport der myTestSkipCol2.dat Datendatei im SQL Server Management Studio-Abfrage-Editor den folgenden Code aus:

USE AdventureWorks2012;
GO
BULK INSERT myTestSkipCol 
   FROM 'C:\myTestSkipCol2.dat' 
   WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');
GO
SELECT * FROM myTestSkipCol;
GO

Verwenden einer XML-Formatdatei

Mit einer XML-Formatdatei können Sie eine Spalte nicht überspringen, wenn Sie mithilfe eines bcp-Befehls oder einer BULK INSERT-Anweisung direkt in eine Tabelle importieren. Sie können jedoch in alle Spalten außer der letzten einer Tabelle importieren. Wenn Sie aber die letzte Spalte überspringen müssen, müssen Sie eine Ansicht der Zieltabelle erstellen, die nur die Spalten enthält, die in der Datendatei enthalten sind. Anschließend können Sie Daten aus dieser Datei massenweise in die Ansicht importieren.

Um eine XML-Formatdatei zu verwenden, um eine Tabellenspalte mit OPENROWSET(BULK...) zu überspringen, müssen Sie eine explizite Liste der Spalten sowohl in der Abfrageliste als auch in der Zieltabelle angeben, wie folgt:

EINFÜGEN ... <column_list> WÄHLEN <column_list> VON OPENROWSET(BULK...)

Erstellen einer XML-Standardformatdatei

Die Beispiele für geänderte Formatdateien basieren auf der myTestSkipCol Beispieltabelle und datendatei, die in "Beispieltabelle und Datendatei" weiter oben in diesem Thema erstellt werden. Mit dem folgenden bcp-Befehl wird eine XML-Standardformatdatei für die myTestSkipCol Tabelle erstellt:

bcp AdventureWorks2012..myTestSkipCol format nul -f myTestSkipCol_Default.xml -c -x -T

Die resultierende Standardformatdatei ohne XML beschreibt eine 1:1-Entsprechung zwischen Datendateifeldern und Tabellenspalten wie folgt:

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

Hinweis

Informationen zur Struktur von XML-Formatdateien finden Sie unter XML-Formatdateien (SQL Server).

Beispiele

Die Beispiele in diesem Abschnitt verwenden die myTestSkipCol Beispieltabelle und die myTestSkipCol2.dat Beispieldatendatei, die weiter oben in diesem Thema unter "Beispieltabelle und Datendatei" erstellt werden. Um die Daten aus myTestSkipCol2.dat der myTestSkipCol Tabelle zu importieren, verwenden die Beispiele die folgende geänderte XML-Formatdatei: myTestSkipCol2-x.xml Dies basiert auf der Formatdatei, die weiter oben in diesem Thema unter "Erstellen einer XML-Standardformatdatei" erstellt wird.

<?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="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Verwenden von OPENROWSET(BULK...)

Im folgenden Beispiel werden der OPENROWSET Massenzeilenanbieter und die myTestSkipCol2.xml Formatdatei verwendet. Das Beispiel importiert die myTestSkipCol2.dat Datendatei in die myTestSkipCol Tabelle. Die Anweisung enthält eine explizite Liste von Spalten in der Auswahlliste und auch in der Zieltabelle, wie erforderlich.

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

USE AdventureWorks2012;
GO
INSERT INTO myTestSkipCol
  (Col1,Col3)
    SELECT Col1,Col3
      FROM  OPENROWSET(BULK  'C:\myTestSkipCol2.Dat',
      FORMATFILE='C:\myTestSkipCol2.Xml'  
       ) as t1 ;
GO

MASSENIMPORT in einer Ansicht verwenden

Im folgenden Beispiel wird v_myTestSkipCol auf der myTestSkipCol-Tabelle erstellt. In dieser Ansicht wird die zweite Tabellenspalte übersprungen. Col2 Die Beispieldatei verwendet dann BULK INSERT, um die myTestSkipCol2.dat Datendatei in diese Ansicht zu importieren.

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

CREATE VIEW v_myTestSkipCol AS
    SELECT Col1,Col3
    FROM myTestSkipCol;
GO

USE AdventureWorks2012;
GO
BULK INSERT v_myTestSkipCol
FROM 'C:\myTestSkipCol2.dat'
WITH (FORMATFILE='C:\myTestSkipCol2.xml');
GO

Siehe auch

bcp UtilityBULK INSERT (Transact-SQL)OPENROWSET (Transact-SQL)Verwenden einer Formatdatei zum Überspringen eines Datenfelds (SQL Server)Verwenden Einer Formatdatei zum Zuordnen von Tabellenspalten zu Data-File Feldern (SQL Server)Verwenden einer Formatdatei zum Massenimport von Daten (SQL Server)

  • Last updated on