Auslassen eines Datenfelds mithilfe einer Formatdatei (SQL Server)

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

Eine Datendatei kann mehr Felder enthalten, als Spalten in der Tabelle vorhanden sind. In diesem Artikel werden das Ändern von Nicht-XML- und XML-Formatdateien beschrieben, um eine Datendatei mit weiteren Feldern aufzunehmen, indem die Tabellenspalten den entsprechenden Datenfeldern zugeordnet und die zusätzlichen Felder ignoriert werden.

Weitere Informationen finden Sie unter Erstellen einer Formatdatei mit bcp (SQL Server).

Hinweis

Eine Nicht-XML- oder XML-Formatdatei kann zum Massenimport einer Datendatei in die Tabelle mithilfe eines bcp-Hilfsprogramms , einer BULK INSERT (Transact-SQL) -Anweisung oder INSERT ... SELECT * FROM OPENROWSET BULK (Transact-SQL) Anweisung . Weitere Informationen finden Sie unter Verwenden einer Formatdatei zum Massenimport von Daten (SQL Server).

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.

Beispieltestbedingungen

Die Beispiele für geänderte Formatdateien in diesem Artikel basieren auf der Beispieltabelle myTestSkipField und datendatei D:\BCP\myTestSkipField.bcp. Ändern Sie den lokalen Dateispeicherort im Codebeispiel in einen Dateispeicherort auf Ihrem Computer.

Beispieltabelle

Das Skript erstellt eine Testdatenbank und eine Tabelle mit dem Namen 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

Erstellen Sie die Formatdateien

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-Dienstprogrammzu 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 Sie eine Nicht-XML-Formatdatei

Ausführliche Informationen finden Sie unter Verwendung von Nicht-XML-Formatdateien (SQL Server ). Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die Nicht-XML-Formatdatei myTestSkipField.fmtzu erstellen, die auf dem Schema von myTestSkipFieldbasiert. 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

Nach

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:

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

Erstellen Sie eine XML-Formatdatei

Überprüfen Sie XML-Formatdateien (SQL Server) auf ausführliche Informationen. Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die XML-Formatdatei myTestSkipField.xmlzu erstellen, die auf dem Schema von myTestSkipFieldbasiert.

• Der Qualifizierer c wird verwendet, um Zeichendaten anzugeben.
• t, wird verwendet, um ein Komma als Feldterminator anzugeben.
• T wird verwendet, um eine vertrauenswürdige Verbindung mit integrierter Sicherheit anzugeben.
• 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 Sie die 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. Erhöhen Sie den FIELD ID Wert für das neue FIELD und für die einzelnen nachfolgenden FIELDWerte um 1.
3. Erhöhen Sie den COLUMN SOURCE Wert um 1 für FirstName, und LastName spiegeln Sie die überarbeitete Zuordnung wider.

Vergleichen Sie die vorgenommenen Änderungen:

Vorher

<?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 entspricht nun Folgendem:

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

Importieren Sie Daten mithilfe einer Formatdatei zum Überspringen eines Datenfelds

In den Beispielen werden die in diesem Artikel erstellten Beispieldatenbank-, Datendatei- und Formatdateien verwendet.

Verwenden Sie die bcp und die 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 XML-Formatdateien (SQL Server)

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 (Transact-SQL) und 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 (Transact-SQL) und XML-Formatdateien (SQL Server)

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 (Transact-SQL) und 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 (Transact-SQL) und XML-Formatdateien (SQL Server)

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;

Verwandte Inhalte

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