Auslassen eines Datenfelds mithilfe einer Formatdatei (SQL Server)

 

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. Weitere Informationen finden Sie unter Erstellen einer Formatdatei (SQL Server).

Outline
Beispieltestbedingungen
 ● Beispieltabelle
 ● Beispieldatendatei
Erstellen der Formatdateien
 ● Erstellen einer Nicht-XML-Formatdatei
 ● Ändern einer Nicht-XML-Formatdatei
 ● Erstellen einer XML-Formatdatei
 ● Ändern einer XML-Formatdatei
Importieren von Daten mithilfe einer Formatdatei zum Überspringen eines Datenfelds
 ● Verwenden von bcp und der Nicht-XML-Formatdatei
 ● Verwenden von bcp und der XML-Formatdatei
 ● Verwenden von BULK INSERT und der Nicht-XML-Formatdatei
 ● Verwenden von BULK INSERT und der XML-Formatdatei
 ● Verwenden von OPENROWSET(BULK...) und der Nicht-XML-Formatdatei
 ● Verwenden von OPENROWSET(BULK...) und der XML-Formatdatei

                                                                                                                                                                                                                  

System_CAPS_ICON_note.jpg Hinweis

Mithilfe einer Nicht-XML- oder XML-Formatdatei kann der Massenimport einer Datendatei in die Tabelle ausgeführt werden. Hierzu kann ein bcp-Hilfsprogramm-Befehl, eine BULK INSERT- oder eine INSERT ... SELECT * FROM OPENROWSET(BULK...)-Anweisung verwendet werden. Weitere Informationen finden Sie unter Massenimport von Daten mithilfe einer Formatdatei (SQL Server).

Die in diesem Thema enthaltenen Beispiele der geänderten Formatdateien basieren auf der Tabelle und der Datendatei, die nachstehend definiert sind.

Beispieltabelle

Das folgende Skript erstellt eine Testdatenbank und eine Tabelle namens myTestSkipField. Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

Beispieldatendatei

Erstellen Sie die leere Datei D:\BCP\myTestSkipField.bcp, und fügen Sie die folgenden Daten ein:

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

Wenn ein Massenimport für Daten aus myTestSkipField.bcp 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.

Die einfachste Methode zum Erstellen der Formatdatei besteht darin, das bcp-Dienstprogramm zu verwenden. Erstellen Sie zunächst eine Basisformatdatei aus der vorhandenen Tabelle. Ändern Sie dann die Basisformatdatei so, dass sie der tatsächlichen Datendatei entspricht.

Erstellen einer Nicht-XML-Formatdatei

Ausführliche Informationen finden Sie unter Nicht-XML-Formatdateien (SQL Server). Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die Nicht-XML-Formatdatei myTestSkipField.fmt zu erstellen, die auf dem Schema von myTestSkipField basiert. Außerdem wird der Qualifizierer c verwendet, um Zeichendaten anzugeben, wird t, verwendet, um ein Komma als Feldabschlusszeichen anzugeben, und wird T verwendet, um eine vertrauenswürdige Verbindung anzugeben, für die integrierte Sicherheit verwendet wird. Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

bcp TestDatabase.dbo.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

Ändern der Nicht-XML-Formatdatei

Informationen zur Terminologie finden Sie unter Struktur von Nicht-XML-Formatdateien. Öffnen Sie D:\BCP\myTestSkipField.fmt in Editor, und nehmen Sie die folgenden Änderungen vor:

  1. Kopieren Sie die gesamte Formatdateizeile für FirstName, und fügen Sie sie unmittelbar nach FirstName in der nächsten Zeile ein.
  2. Setzen Sie den Wert für die Reihenfolge der Felder der Hostdatei für die neue Zeile und alle nachfolgenden Zeilen um eins herauf.
  3. Erhöhen Sie den Wert für die Anzahl der Spalten, um die tatsächliche Anzahl der Felder in der Datendatei anzugeben.
  4. Ändern Sie die Spaltenreihenfolge auf dem Server für die zweite Zeile der Formatdatei von 2 in 0.

Vergleichen Sie die vorgenommenen Änderungen:
Vorher

13.0
3
1       SQLCHAR 0       7       ","      1     PersonID     ""
2       SQLCHAR 0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR 0       30      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

Nachher

13.0
4
1       SQLCHAR 0       7       ","      1     PersonID     ""
2       SQLCHAR 0       25      ","      0     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR 0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR 0       50      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS


Die geänderte Formatdatei berücksichtigt nun:

  • 4 Datenfelder
  • Das erste Datenfeld in myTestSkipField.bcp ist der ersten Spalte zugeordnet, myTestSkipField.. PersonID
  • Das zweite Datenfeld in myTestSkipField.bcp ist keiner Spalte zugeordnet.
  • Das dritte Datenfeld in myTestSkipField.bcp ist der zweiten Spalte zugeordnet, myTestSkipField.. FirstName
  • Das vierte Datenfeld in myTestSkipField.bcp ist der dritten Spalte zugeordnet, myTestSkipField.. LastName

Erstellen einer XML-Formatdatei

Ausführliche Informationen finden Sie unter XML-Formatdateien (SQL Server). Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die XML-Formatdatei myTestSkipField.xml zu erstellen, die auf dem Schema von myTestSkipField basiert. Außerdem wird der Qualifizierer c verwendet, um Zeichendaten anzugeben, wird t, verwendet, um ein Komma als Feldabschlusszeichen anzugeben, und wird T verwendet, um eine vertrauenswürdige Verbindung anzugeben, für die integrierte Sicherheit verwendet wird. Der x-Qualifizierer muss verwendet werden, um eine XML-basierte Formatdatei zu generieren. Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

bcp TestDatabase.dbo.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.xml -t, -T

Ändern der XML-Formatdatei

Informationen zur Terminologie finden Sie unter Schemasyntax für XML-Formatdateien. Öffnen Sie D:\BCP\myTestSkipField.xml in Editor, und nehmen Sie die folgenden Änderungen vor:

  1. Kopieren Sie das gesamte zweite Feld, und fügen Sie es unmittelbar nach dem zweiten Feld in der nächsten Zeile ein.
  2. Setzen Sie den Wert von "FIELD ID"für das neue Feld FIELD und für jedes nachfolgende FIELD um 1 herauf.
  3. Erhöhen Sie den Wert von "COLUMN SOURCE" für FirstName und LastName um 1, um der geänderten Zuordnung Rechnung zu tragen.

Vergleichen Sie die vorgenommenen Änderungen:
vor

<?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="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

Nach

<?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="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

Die geänderte Formatdatei berücksichtigt nun:

  • 4 Datenfelder
  • FIELD 1, das COLUMN 1 entspricht, wird der ersten Tabellenspalte zugeordnet, myTestSkipField.. PersonID
  • FIELD 2 entspricht keiner COLUMN und wird daher keiner Tabellenspalte zugeordnet.
  • FIELD 3, das COLUMN 3 entspricht, wird der zweiten Tabellenspalte zugeordnet, myTestSkipField.. FirstName
  • FIELD 4, das COLUMN 4 entspricht, wird der dritten Tabellenspalte zugeordnet, myTestSkipField.. LastName

In den folgenden Beispielen werden die Datenbank, die Datendatei und die Formatdateien verwendet, die oben erstellt wurden.

Verwenden von bcp und der Nicht-XML-Formatdatei

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T

Verwenden von bcp und der XML-Formatdatei

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.xml -T

Verwenden von BULK INSERT und der Nicht-XML-Formatdatei

Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField   
   FROM 'D:\BCP\myTestSkipField.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.fmt');  
GO  

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Verwenden von BULK INSERT und der XML-Formatdatei

Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField   
   FROM 'D:\BCP\myTestSkipField.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.xml');  
GO  

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Verwenden von OPENROWSET(BULK...) und der Nicht-XML-Formatdatei

Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:

USE TestDatabase;
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Verwenden von OPENROWSET(BULK...) und der XML-Formatdatei

Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField 
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

bcp (Hilfsprogramm)
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Überspringen einer Tabellenspalte mithilfe einer Formatdatei (SQL Server)
Verwenden einer Formatdatei zum Zuordnen von Tabellenspalten zu Datendateifeldern (SQL Server)

Community-Beiträge

HINZUFÜGEN
Anzeigen: