Verwenden einer Formatdatei zum Zuordnen von Tabellenspalten zu Datendateifeldern (SQL Server)

  • Artikel

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

Eine Datendatei kann Felder in einer anderen Reihenfolge als die der entsprechenden Spalten in der Tabelle aufweisen. In diesem Thema werden Nicht-XML-Formatdateien und XML-Formatdateien dargestellt, die zum Anpassen an eine Datendatei, deren Felder eine andere Reihenfolge als die Tabellenspalten aufweisen, geändert wurden. Die geänderte Formatdatei ordnet die Datenfelder den entsprechenden Tabellenspalten zu. Weitere Informationen finden Sie unter Erstellen einer Formatdatei (SQL Server) .

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).

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 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 myRemap. Führen Sie den folgenden Transact-SQL-Befehl in Microsoft SQL Server Management Studio (SSMS) aus:

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myRemap
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   Gender char(1)
   );

Beispieldatendatei

Die nachstehenden Daten entsprechen FirstName und LastName in umgekehrter Reihenfolge, wie in der Tabelle myRemapdargestellt. Erstellen Sie mit Editor die leere Datei D:\BCP\myRemap.bcp , und fügen Sie die folgenden Daten ein:

1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F

Erstellen Sie die Formatdateien

Wenn ein Massenimport für Daten aus myRemap.bcp in die myRemap -Tabelle ausgeführt werden soll, muss die Formatdatei folgende Funktionen ausführen:

  • Der ersten Spalte ( PersonID) das erste Datenfeld zuordnen.
  • Der dritten Spalte ( LastName) das zweite Datenfeld zuordnen.
  • Der zweiten Spalte ( FirstName) das dritte Datenfeld zuordnen.
  • Der vierten Spalte ( Gender) 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 Nicht-XML-Formatdateien (SQL Server) . Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die Nicht-XML-Formatdatei myRemap.fmtzu erstellen, die auf dem Schema von myRemapbasiert. 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.myRemap format nul -c -f D:\BCP\myRemap.fmt -t, -T

Ändern der Nicht-XML-Formatdatei

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

  1. Ändern Sie die Reihenfolge der Zeilen in der Formatdatei, sodass die Zeilen in derselben Reihenfolge vorliegen wie die Daten in myRemap.bcp.
  2. Stellen Sie sicher, dass die Werte für die Reihenfolge der Hostfelder sequenziell sind.
  3. Stellen Sie sicher, dass nach der letzten Zeile der Formatdatei ein Zeilenumbruch eingefügt wird.

Vergleichen Sie die Änderungen:

Vorher

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

Nachher

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

Die geänderte Formatdatei entspricht nun Folgendem:

  • Das erste Datenfeld in myRemap.bcp wird der ersten Spalte zugeordnet, myRemap.. PersonID
  • Das zweite Datenfeld in myRemap.bcp wird der dritten Spalte zugeordnet, myRemap.. LastName
  • Das dritte Datenfeld in myRemap.bcp wird der zweiten Spalte zugeordnet, myRemap.. FirstName
  • Das vierte Datenfeld in myRemap.bcp wird der vierten Spalte zugeordnet, myRemap.. Gender

Erstellen Sie eine XML-Formatdatei

Ausführliche Informationen finden Sie unter XML-Formatdateien (SQL Server) . Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die XML-Formatdatei myRemap.xmlzu erstellen, die auf dem Schema von myRemapbasiert. 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.myRemap format nul -c -x -f D:\BCP\myRemap.xml -t, -T

Ändern Sie die XML-Formatdatei

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

  1. Die Reihenfolge, in der die <FIELD>-Elemente in der Formatdatei deklariert sind, ist die Reihenfolge, in der diese Felder in der Datendatei angezeigt werden. Kehren Sie daher die Reihenfolge für die <FIELD>-Elemente mit den ID-Attributen 2 und 3 um.
  2. Stellen Sie sicher, dass die Werte des <FIELD>ID-Attributs sequenziell sind.
  3. Die Reihenfolge der <COLUMN>-Elemente im <ROW>-Element definiert, in welcher Reihenfolge diese vom Massenvorgang an das Ziel gesendet werden. Die XML-Formatdatei weist jedem <COLUMN>-Element einen lokalen Namen zu, der keine Verbindung zu der Spalte in der Zieltabelle des Massenimportvorgangs aufweist. Die Reihenfolge der <COLUMN>-Elemente ist unabhängig von der Reihenfolge der <FIELD>-Elemente in einer <RECORD>-Definition. Jedes <COLUMN>-Element entspricht einem <FIELD>-Element (dessen ID im SOURCE-Attribut des <COLUMN>-Elements angegeben ist). Somit sind die Werte für <COLUMN> SOURCE die einzigen Attribute, die eine Überarbeitung erfordern. Kehren Sie die Reihenfolge für die <COLUMN> SOURCE-Attribute 2 und 3 um.

Vergleichen Sie die Ä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="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" 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"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>

Nachher

<?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="30" 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="1" 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="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>

Die geänderte Formatdatei entspricht nun Folgendem:

  • FIELD 1, das COLUMN 1 entspricht, wird der ersten Tabellenspalte zugeordnet, myRemap.. PersonID
  • FIELD 2, das COLUMN 2 entspricht, wird der dritten Tabellenspalte neu zugeordnet, myRemap.. LastName
  • FIELD 3, das COLUMN 3 entspricht, wird der zweitten Tabellenspalte neu zugeordnet, myRemap.. FirstName
  • FIELD 4, das COLUMN 4 entspricht, wird der vierten Tabellenspalte zugeordnet, myRemap.. Gender

Importieren Sie Daten mit einer Formatdatei, um Tabellenspalten zu Datendateifeldern zuzuordnen

Die nachstehenden Beispiele verwenden die oben erstellte Datenbank, Datendatei und Formatdatei.

Verwenden Sie die bcp und die Nicht-XML-Formatdatei

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

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

Verwenden Sie die bcp und die XML-Formatdatei

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

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

Verwenden Sie BULK INSERT und die Nicht-XML-Formatdatei

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

USE TestDatabase;  
GO

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

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

Verwenden Sie BULK INSERT und die XML-Formatdatei

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

USE TestDatabase;  
GO

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

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

Verwenden Sie OPENROWSET(BULK...) und die Nicht-XML-Formatdatei

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

USE TestDatabase;
GO

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

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

Verwenden Sie OPENROWSET(BULK...) und die XML-Formatdatei

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

USE TestDatabase;  
GO

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

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

Nächste Schritte