Auslassen eines Datenfelds mithilfe einer Formatdatei (SQL Server)Use a format file to skip a data field (SQL Server)

  • 11 Minuten Lesedauer

Gilt für: JaSQL Server JaAzure SQL-Datenbank JaAzure Synapse Analytics (SQL DW) JaParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Eine Datendatei kann mehr Felder enthalten, als Spalten in der Tabelle vorhanden sind.A data file can contain more fields than the number of columns in the table. 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.This topic describes modifying both non-XML and XML format files to accommodate a data file with more fields by mapping the table columns to the corresponding data fields and ignoring the extra fields. Weitere Informationen finden Sie unter Erstellen einer Formatdatei (SQL Server) .Please review Create a Format File (SQL Server) for additional information.

OutlineOutline
BeispieltestbedingungenExample Test Conditions
 ● Beispieltabelle ● Sample Table
 ● Beispieldatendatei ● Sample Data File
Erstellen der FormatdateienCreating the Format Files
 ● Erstellen einer Nicht-XML-Formatdatei ● Creating a Non-XML Format File
 ● Ändern einer Nicht-XML-Formatdatei ● Modifying a Non-XML Format File
 ● Erstellen einer XML-Formatdatei ● Creating an XML Format File
 ● Ändern einer XML-Formatdatei ● Modifying an XML Format File
Importieren von Daten mithilfe einer Formatdatei zum Überspringen eines DatenfeldsImporting Data with a Format File to skip a Data Field
 ● Verwenden von BCP- und der Nicht-XML-Formatdatei ● Using bcp and Non-XML Format File
 ● Verwenden von BCP und der XML-Formatdatei ● Using bcp and XML Format File
 ● Verwenden von BULK INSERT und der Nicht-XML-Formatdatei ● Using BULK INSERT and Non-XML Format File
 ● Verwenden von BULK INSERT und der XML-Formatdatei ● Using BULK INSERT and XML Format File
 ● Verwenden von OPENROWSET(BULK...) und der Nicht-XML-Formatdatei ● Using OPENROWSET(BULK...) and Non-XML Format File
 ● Verwenden von OPENROWSET(BULK...) und der XML-Formatdatei ● Using OPENROWSET(BULK...) and XML Format File

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.Either a non-XML or XML format file can be used to bulk import a data file into the table by using a bcp utility command, BULK INSERT statement, or INSERT ... SELECT * FROM OPENROWSET(BULK...) statement. Weitere Informationen finden Sie unter Massenimport von Daten mithilfe einer Formatdatei (SQL Server).For more information, see Use a Format File to Bulk Import Data (SQL Server).

BeispieltestbedingungenExample Test Conditions

Die in diesem Thema enthaltenen Beispiele der geänderten Formatdateien basieren auf der Tabelle und der Datendatei, die nachstehend definiert sind.The examples of modified format files in this topic are based on the table and data file defined below.

BeispieltabelleSample Table

Das folgende Skript erstellt eine Testdatenbank und eine Tabelle namens myTestSkipField.The script below creates a test database and a table named myTestSkipField. Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

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

BeispieldatendateiSample Data File

Erstellen Sie die leere Datei D:\BCP\myTestSkipField.bcp , und fügen Sie die folgenden Daten ein:Create an empty file D:\BCP\myTestSkipField.bcp and insert the following data:

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

Erstellen der FormatdateienCreating the Format Files

Wenn ein Massenimport für Daten aus myTestSkipField.bcp in die myTestSkipField -Tabelle ausgeführt werden soll, muss die Formatdatei folgende Funktionen ausführen:To bulk import data from myTestSkipField.bcp into the myTestSkipField table, the format file must do the following:

  • Der ersten Spalte ( PersonID) das erste Datenfeld zuordnen.Map the first data field to the first column, PersonID.
  • Das zweite Datenfeld auslassen.Skip the second data field.
  • Der zweiten Spalte ( FirstName) das dritte Datenfeld zuordnen.Map the third data field to the second column, FirstName.
  • Der dritten Spalte ( LastName) das vierte Datenfeld zuordnen.Map the fourth data field to the third column, LastName.

Die einfachste Methode zum Erstellen der Formatdatei besteht darin, das bcp-Dienstprogrammzu verwenden.The simplest method to create the format file is by using the bcp utility. Erstellen Sie zunächst eine Basisformatdatei aus der vorhandenen Tabelle.First, create a base format file from the existing table. Ändern Sie dann die Basisformatdatei so, dass sie der tatsächlichen Datendatei entspricht.Second, modify the base format file to reflect the actual data file.

Erstellen einer Nicht-XML-FormatdateiCreating a Non-XML Format File

Ausführliche Informationen finden Sie unter Nicht-XML-Formatdateien (SQL Server) .Please review Non-XML Format Files (SQL Server) for detailed information. Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die Nicht-XML-Formatdatei myTestSkipField.fmtzu erstellen, die auf dem Schema von myTestSkipFieldbasiert.The following command will use the bcp utility to generate a non-xml format file, myTestSkipField.fmt, based on the schema of myTestSkipField. 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.In addition, the qualifier c is used to specify character data , t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

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

Ändern der Nicht-XML-Formatdatei Modifying the Non-XML Format File

Informationen zur Terminologie finden Sie unter Struktur von Nicht-XML-Formatdateien .See Structure of Non-XML Format Files for terminology. Öffnen Sie D:\BCP\myTestSkipField.fmt in Editor, und nehmen Sie die folgenden Änderungen vor:Open D:\BCP\myTestSkipField.fmt in Notepad and perform the following modifications:

  1. Kopieren Sie die gesamte Formatdateizeile für FirstName , und fügen Sie sie unmittelbar nach FirstName in der nächsten Zeile ein.Copy the entire format-file row for FirstName and paste it directly after FirstName on the next line.
  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.Increase the host file field order value by one for the new row and all subsequent rows.
  3. Erhöhen Sie den Wert für die Anzahl der Spalten, um die tatsächliche Anzahl der Felder in der Datendatei anzugeben.Increase the number of columns value to reflect the actual number of fields in the data file.
  4. Ändern Sie die Spaltenreihenfolge auf dem Server für die zweite Zeile der Formatdatei von 2 in 0 .Modify the server column order from 2 to 0 for the second format-file row.

Vergleichen Sie die vorgenommenen Änderungen:Compare the changes made:
VorherBefore

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

NachAfter

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 entspricht nun Folgendem:The modified format file now reflects:

  • 4 Datenfelder4 data fields
  • Das erste Datenfeld in myTestSkipField.bcp ist der ersten Spalte zugeordnet, myTestSkipField.. PersonIDThe first data field in myTestSkipField.bcp is mapped to the first column, myTestSkipField.. PersonID
  • Das zweite Datenfeld in myTestSkipField.bcp ist keiner Spalte zugeordnet.The second data field in myTestSkipField.bcp is not mapped to any column.
  • Das dritte Datenfeld in myTestSkipField.bcp ist der zweiten Spalte zugeordnet, myTestSkipField.. FirstNameThe third data field in myTestSkipField.bcp is mapped to the second column, myTestSkipField.. FirstName
  • Das vierte Datenfeld in myTestSkipField.bcp ist der dritten Spalte zugeordnet, myTestSkipField.. LastNameThe fourth data field in myTestSkipField.bcp is mapped to the third column, myTestSkipField.. LastName

Erstellen einer XML-Formatdatei Creating an XML Format File

Ausführliche Informationen finden Sie unter XML-Formatdateien (SQL Server) .Please review XML Format Files (SQL Server) for detailed information. Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die XML-Formatdatei myTestSkipField.xmlzu erstellen, die auf dem Schema von myTestSkipFieldbasiert.The following command will use the bcp utility to create an xml format file, myTestSkipField.xml, based on the schema of myTestSkipField. 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.In addition, the qualifier c is used to specify character data , t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. Der x -Qualifizierer muss verwendet werden, um eine XML-basierte Formatdatei zu generieren.The x qualifier must be used to generate an XML-based format file. Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

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

Ändern der XML-Formatdatei Modifying the XML Format File

Informationen zur Terminologie finden Sie unter Schemasyntax für XML-Formatdateien .See Schema Syntax for XML Format Files for terminology. Öffnen Sie D:\BCP\myTestSkipField.xml in Editor, und nehmen Sie die folgenden Änderungen vor:Open D:\BCP\myTestSkipField.xml in Notepad and perform the following modifications:

  1. Kopieren Sie das gesamte zweite Feld, und fügen Sie es unmittelbar nach dem zweiten Feld in der nächsten Zeile ein.Copy the entire second field and paste it directly after the second field on the next line.
  2. Setzen Sie den Wert von "FIELD ID"für das neue Feld FIELD und für jedes nachfolgende FIELD um 1 herauf.Increase the "FIELD ID" value by 1 for the new FIELD and for each subsequent FIELD.
  3. Erhöhen Sie den Wert von "COLUMN SOURCE" für FirstNameund LastName um 1, um der geänderten Zuordnung Rechnung zu tragen.Increase the "COLUMN SOURCE" value by 1 for FirstName, and LastName to reflect the revised mapping.

Vergleichen Sie die vorgenommenen Änderungen:Compare the changes made:
VorherBefore

\<?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="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>

NachAfter

\<?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="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 entspricht nun Folgendem:The modified format file now reflects:

  • 4 Datenfelder4 data fields
  • FIELD 1, das COLUMN 1 entspricht, wird der ersten Tabellenspalte zugeordnet, myTestSkipField.. PersonIDFIELD 1 which corresponds to COLUMN 1 is mapped to the first table column, myTestSkipField.. PersonID
  • FIELD 2 entspricht keiner COLUMN und wird daher keiner Tabellenspalte zugeordnet.FIELD 2 does not correspond to any COLUMN and thus, is not mapped to any table column.
  • FIELD 3, das COLUMN 3 entspricht, wird der zweiten Tabellenspalte zugeordnet, myTestSkipField.. FirstNameFIELD 3 which corresponds to COLUMN 3 is mapped to the second table column, myTestSkipField.. FirstName
  • FIELD 4, das COLUMN 4 entspricht, wird der dritten Tabellenspalte zugeordnet, myTestSkipField.. LastNameFIELD 4 which corresponds to COLUMN 4 is mapped to the third table column, myTestSkipField.. LastName

Importieren von Daten mithilfe einer Formatdatei zum Überspringen eines DatenfeldsImporting Data with a Format File to skip a Data Field

Die nachstehenden Beispiele verwenden die oben erstellte Datenbank, Datendatei und Formatdatei.The examples below use the database, datafile, and format files created above.

Verwenden von bcp und der Nicht-XML-FormatdateiUsing bcp and Non-XML Format File

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

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

Verwenden von bcp und der XML-FormatdateiUsing bcp and XML Format File

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

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

Verwenden von BULK INSERT und der Nicht-XML-FormatdateiUsing BULK INSERT and Non-XML Format File

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

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-FormatdateiUsing BULK INSERT and XML Format File

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

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-FormatdateiUsing OPENROWSET(BULK...) and Non-XML Format File

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

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-FormatdateiUsing OPENROWSET(BULK...) and XML Format File

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

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;

Weitere InformationenSee Also

bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
Überspringen einer Tabellenspalte mithilfe einer Formatdatei (SQL Server) Use a Format File to Skip a Table Column (SQL Server)
Verwenden einer Formatdatei zum Zuordnen von Tabellenspalten zu Datendateifeldern (SQL Server)Use a Format File to Map Table Columns to Data-File Fields (SQL Server)