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

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Eine Datendatei kann Felder in einer anderen Reihenfolge als die der entsprechenden Spalten in der Tabelle aufweisen. In diesem Artikel werden sowohl Nicht-XML- als auch XML-Formatdateien dargestellt, die geändert werden, um eine Datendatei aufzunehmen, deren Felder in einer anderen Reihenfolge als die Tabellenspalten angeordnet sind. Die geänderte Formatdatei ordnet die Datenfelder den entsprechenden Tabellenspalten zu. Weitere Informationen finden Sie unter Erstellen einer Formatdatei mit bcp (SQL Server ).

Eine Nicht-XML- oder XML-Formatdatei kann zum Massenimport einer Datendatei in die Tabelle verwendet werden, mit einem bcp-Hilfsprogrammbefehl, einer BULK INSERT-Anweisung oder einer INSERT -Anweisung ... SELECT * FROM OPENROWSET-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 Tabellen- und Datendatei, die weiter unten in diesem Artikel definiert wurde.

Beispieltabelle

Das folgende Transact-SQL-Skript erstellt eine Testdatenbank und eine Tabelle mit dem Namen myRemap.

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;

CREATE TABLE myRemap
(
    PersonID SMALLINT,
    FirstName VARCHAR (25),
    LastName VARCHAR (30),
    Gender CHAR (1)
);

Beispieldatendatei

Die folgenden Daten werden wie FirstName in der Tabelle myRemapdargestellt und LastName in umgekehrter Reihenfolge dargestellt. Erstellen Sie mithilfe eines Texteditors wie Editor eine 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

Zum Massenimport von Daten aus myRemap.bcp der myRemap Tabelle muss die Formatdatei die folgenden Aktionen 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 Verwendung von Nicht-XML-Formatdateien (SQL Server ). Der folgende Befehl verwendet das bcp-Hilfsprogramm , um eine Nicht-XML-Formatdatei myRemap.fmtbasierend auf dem Schema von myRemap. 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. Ordnen Sie die Reihenfolge der Formatdateizeilen neu an, sodass sich die Zeilen in derselben Reihenfolge wie die Daten befinden 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 Formatdateizeile ein Wagenrücklauf vorhanden ist.

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

After

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). Der folgende Befehl verwendet das bcp-Hilfsprogramm zum Erstellen einer XML-Formatdatei, myRemap.xmlbasierend auf dem Schema von myRemap. 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 werden, ist die Reihenfolge, in der diese Felder in der Datendatei angezeigt werden. Umkehren Sie daher die Reihenfolge für die Elemente mit den <FIELD> ID-Attributen 2 und 3.

  2. Stellen Sie sicher, dass die Werte des <FIELD>-Attributs ID sequenziell sind.

  3. Die Reihenfolge der <COLUMN> Elemente im <ROW-Element> definiert die Reihenfolge, in der der Massenvorgang sie an das Ziel sendet. Die XML-Formatdatei weist jedem <COLUMN>-Element einen lokalen Namen zu, der keine Beziehung 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 überarbeitet werden müssen. Kehren Sie die Reihenfolge für die <COLUMN>-Attribute SOURCE 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>

After

<?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
  • FELD 2, das SPALTE 2 entspricht, wird der dritten Tabellenspalte neu zugeordnet, myRemap.. LastName
  • FELD 3, das SPALTE 3 entspricht, wird der zweiten 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

In den folgenden Beispielen werden die zuvor erstellten Datenbank-, Datendatei- und Formatdateien verwendet.

Verwenden Sie die bcp und die Nicht-XML-Formatdatei

Geben Sie an einer Eingabeaufforderung den folgenden Befehl 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 an einer Eingabeaufforderung den folgenden Befehl 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 das folgende Transact-SQL-Skript 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 das folgende Transact-SQL-Skript 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 der OPENROWSET- und Nicht-XML-Formatdatei

Führen Sie das folgende Transact-SQL-Skript 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 der OPENROWSET- und XML-Formatdatei

Führen Sie das folgende Transact-SQL-Skript 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;

Zugehöriger Inhalt