Überspringen einer Tabellenspalte mithilfe einer Formatdatei (SQL Server)

  • Artikel

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

In diesem Artikel wird beschrieben, wie eine Formatdatei zum Überspringen des Imports einer Tabellenspalte verwendet wird, wenn die Daten für die übersprungene Spalte nicht in der Quelldatendatei vorhanden sind. Eine Datendatei kann weniger Felder enthalten als Spalten in der Zieltabelle vorhanden sind. Das bedeutet, dass Sie das Importieren einer Spalte überspringen können – allerdings nur, wenn eine der folgenden zwei Bedingungen in der Zieltabelle erfüllt ist:

  • Für die übersprungene Spalte ist NULL zulässig.
  • Die übersprungene Spalte besitzt einen Standardwert.

Hinweis

Diese Syntax, einschließlich Masseneinfügen, wird in Azure Synapse Analytics nicht unterstützt. Führen Sie in Azure Synapse Analytics und anderen Clouddatenbank-Plattformintegrationen Datenverschiebung über die COPY-Anweisung in Azure Data Factory oder mithilfe von T-SQL-Anweisungen wie COPY INTO und PolyBase aus.

Beispieltabelle und Datendatei

In den Beispielen in diesem Artikel wird eine Tabelle mit dem Namen myTestSkipCol unter dem dbo Schema erwartet. Sie können diese Tabelle in einer Beispieldatenbank wie WideWorldImporters oder AdventureWorks in einer beliebigen anderen Datenbank erstellen. Erstellen Sie diese Tabelle folgendermaßen

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

In den Beispielen in diesem Artikel wird auch eine Beispieldatendatei verwendet: myTestSkipCol2.dat. Diese Datendatei enthält nur zwei Felder, obwohl die Zieltabelle drei Spalten enthält.

1,DataForColumn3  
1,DataForColumn3  
1,DataForColumn3

Grundlegende Schritte

Sie können eine Nicht-XML-Formatdatei oder eine XML-Formatdatei verwenden, um eine Tabellenspalte auszulassen. In beiden Fällen sind zwei Schritte erforderlich:

  1. Verwenden Sie das Befehlszeilenprogramm bcp , um eine Standardformatdatei zu erstellen.
  2. Ändern Sie die Standardformatdatei in einem Text-Editor.

Die geänderte Formatdatei muss jedes vorhandene Feld der jeweiligen Spalte in der Zieltabelle zuordnen. Es muss ebenfalls angegeben werden, welche Tabellenspalte oder -spalten übersprungen werden sollen.

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

Option 1: Verwenden einer Nicht-XML-Formatdatei

Schritt 1: Erstellen einer Nicht-XML-Standardformatdatei

Erstellen Sie eine Nicht-XML-Standardformatdatei für die Beispieltabelle myTestSkipCol, indem Sie den folgenden bcp-Befehl an der Eingabeaufforderung ausführen:

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

Wichtig

Möglicherweise müssen Sie mit dem -S-Argument den Namen der Serverinstanz angeben, mit der Sie eine Verbindung herstellen. Außerdem kann es erforderlich sein, den Benutzernamen und das entsprechende Kennwort mit den Argumenten -U und -P anzugeben. Weitere Informationen finden Sie unter bcp Utility.

Mit dem zuvor angeführten Befehl wird die Nicht-XML-Formatdatei myTestSkipCol_Default.fmterstellt: Diese Formatdatei wird auch als Standardformatdatei bezeichnet. Es handelt sich hierbei um die Form, in der Dateien von bcpgeneriert werden. Mit einer Standardformatdatei wird eine 1:1-Entsprechung zwischen den Feldern in der Datendatei und den Spalten in der Tabelle beschrieben.

Der folgende Screenshot zeigt die Werte in dieser Standardformatdatei des Beispiels.

Ein Diagramm, das die Standardmäßige Nicht-XML-Formatdatei für mytestskipcol enthält.

Hinweis

Weitere Informationen zu den Formatdateifeldern finden Sie unter Nicht-XML-Formatdateien (SQL Server).

Schritt 2: Ändern einer Nicht-XML-Formatdatei

Um eine Nicht-XML-Standardformatdatei zu ändern, gibt es zwei Alternativen. Beide Alternativen zeigen, dass das Datenfeld in der Datendatei nicht vorhanden ist und dass keine Daten in die entsprechende Tabellenspalte eingefügt werden.

Sie können eine Tabellenspalte auslassen, indem Sie eine standardmäßige Nicht-XML-Formatdatei bearbeiten und die Datei mithilfe einer der folgenden alternativen Methoden ändern:

Option 1: Entfernen der Zeile

Die bevorzugte Methode zum Überspringen einer Spalte umfasst die folgenden drei Schritte:

  1. Löschen Sie zuerst alle Formatdateizeilen, in denen Felder beschrieben werden, die in der Quelldatendatei fehlen.
  2. Ändern Sie anschließend den Wert "Reihenfolge der Felder der Hostdatei" für die einzelnen Formatdateizeilen, die auf eine gelöschte Zeile folgen. Das Ziel besteht darin, mithilfe der „Host file field order“-Werte (Reihenfolge der Felder der Hostdatei) von 1 bis ndie eigentliche Position der einzelnen Datenfelder in der Datendatei zu erhalten.
  3. Abschließend muss der Wert im Feld "Spaltenanzahl" entsprechend der tatsächlichen Anzahl der Felder in der Datendatei verringert werden.

Das folgende Beispiel basiert ebenfalls auf der Standardformatdatei für die Tabelle myTestSkipCol. In dieser geänderten Formatdatei wird Col1das erste Datenfeld zugeordnet, Col2wird ausgelassen, und das zweite Datenfeld wird Col3zugeordnet. Die Zeile für Col2 wurde gelöscht. Das Trennzeichen nach dem ersten Feld wurde ebenfalls von \t in , geändert.

14.0  
2  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Option 2: Ändern der Zeilendefinition

Alternativ können Sie eine Tabellenspalte auslassen, indem Sie die Definition der Formatdateizeile ändern, die der Tabellenspalte entspricht. In dieser Formatdateizeile müssen die Werte "prefix length", "host file data length" und "server column order" auf 0 festgelegt werden. Außerdem müssen die Felder „terminator“ und „column collation“ auf „“ (d.h. auf einen leeren oder NULL-Wert) festgelegt werden. Für „server column name“ (Serverspaltenname)muss eine Zeichenfolge eingegeben werden, die nicht leer ist. Dabei muss es sich jedoch auch nicht um den tatsächlichen Spaltennamen handeln. Für die verbleibenden Formatfelder sind die entsprechenden Standardwerte erforderlich.

Das folgende Beispiel wird ebenfalls von der Standardformatdatei der myTestSkipCol -Tabelle abgeleitet.

14.0  
3  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       0       ""       0     Col2         ""  
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Beispiele mit einer Nicht-XML-Formatdatei

Die folgenden Beispiele basieren auf der Beispieltabelle myTestSkipCol und der Beispieldatendatei myTestSkipCol2.dat, die weiter oben in diesem Artikel beschrieben wurden.

Verwenden von BULK INSERT

Dieses Beispiel funktioniert wie im vorherigen Abschnitt beschrieben unter Verwendung einer geänderten erstellten Nicht-XML-Formatdateien. In diesem Beispiel lautet der Name der geänderten Formatdatei myTestSkipCol2.fmt. Führen Sie zum Massenimport BULK INSERT der myTestSkipCol2.dat Datendatei in SQL Server Management Studio (SSMS) den folgenden Code aus. Aktualisieren Sie die Dateisystempfade für den Speicherort der Beispieldateien auf Ihrem Computer.

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

Option 2: Verwenden einer XML-Formatdatei

Schritt 1: Erstellen einer XML-Standardformatdatei

Erstellen Sie eine XML-Standardformatdatei für die Beispieltabelle myTestSkipCol, indem Sie den folgenden bcp-Befehl an der Eingabeaufforderung ausführen:

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

Wichtig

Möglicherweise müssen Sie mit dem -S-Argument den Namen der Serverinstanz angeben, mit der Sie eine Verbindung herstellen. Außerdem kann es erforderlich sein, den Benutzernamen und das entsprechende Kennwort mit den Argumenten -U und -P anzugeben. Weitere Informationen finden Sie unter bcp Utility.

Mit dem zuvor genannten Befehl wird eine XML-Formatdatei namens myTestSkipCol_Default.xml erstellt. Diese Formatdatei wird auch als Standardformatdatei bezeichnet. Es handelt sich hierbei um die Form, in der Dateien von bcpgeneriert werden. Mit einer Standardformatdatei wird eine 1:1-Entsprechung zwischen den Feldern in der Datendatei und den Spalten in der Tabelle beschrieben.

<?xml version="1.0"?>  
<BCPFORMAT xmlns="http://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).

Schritt 2: Ändern einer XML-Formatdatei

Hier ist die geänderte XML-Formatdatei, myTestSkipCol2.xml, dargestellt, die Col2 überspringt. Die Einträge FIELD und ROW für Col2 wurden entfernt, und die Einträge wurden neu nummeriert. Das Trennzeichen nach dem ersten Feld wurde ebenfalls von \t in , geändert.

<?xml version="1.0"?>  
<BCPFORMAT xmlns="http://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>

Beispiele für eine XML-Formatdatei

Die folgenden Beispiele basieren auf der Beispieltabelle myTestSkipCol und der Beispieldatendatei myTestSkipCol2.dat, die weiter oben in diesem Artikel beschrieben wurden.

Zum Importieren der Daten aus myTestSkipCol2.dat in die Tabelle myTestSkipCol wird in den Beispielen die geänderte XML-Formatdatei myTestSkipCol2.xml verwendet.

Verwenden von BULK INSERT mit einer Ansicht

Bei einer XML-Formatdatei ist es nicht möglich, beim direkten Importieren in eine Tabelle mit dem Befehl bcp oder der BULK INSERT-Anweisung eine Spalte auszulassen. Sie können jedoch Daten in alle Spalten einer Tabelle mit Ausnahme der letzten Spalte importieren. Wenn Sie eine andere Spalte als die letzte auslassen müssen, müssen Sie eine Sicht der Zieltabelle erstellen, die nur die in der Datendatei enthaltenen Spalten enthält. Anschließend können Sie Daten aus dieser Datei in die Sicht massenimportieren.

Im folgenden Beispiel wird für die myTestSkipCol-Tabelle die Sicht v_myTestSkipCol erstellt. In dieser Sicht wird die zweite Tabellenspalte, Col2, ausgelassen. Anschließend wird mit BULK INSERT die Datendatei myTestSkipCol2.dat in die Sicht importiert.

Führen Sie den folgenden Code in SSMS aus: Aktualisieren Sie die Dateisystempfade für den Speicherort der Beispieldateien auf Ihrem Computer.

USE WideWorldImporters;  
GO

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

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

Verwenden Von OPENROWSET(BULK...)

Um eine XML-Formatdatei zu verwenden, die unter Verwendung von OPENROWSET(BULK...) eine Tabellenspalte überspringt, muss eine explizite Liste von Spalten in der Auswahlliste und in der Zieltabelle angegeben werden:

INSERT ...<column_list> SELECT <column_list> FROM OPENROWSET(BULK...)

Im folgenden Beispiel werden der Massenrowsetanbieter OPENROWSET und die Formatdatei myTestSkipCol2.xml verwendet. Die Datendatei myTestSkipCol2.dat wird per Massenimport in die myTestSkipCol -Tabelle übertragen. Die Anweisung enthält anforderungsgemäß eine explizite Liste der Spalten in der Auswahlliste und in der Zieltabelle.

Führen Sie den folgenden Code in SSMS aus: Aktualisieren Sie die Dateisystempfade für den Speicherort der Beispieldateien auf Ihrem Computer.

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

Nächste Schritte