• Artikel

Verwenden einer Formatdatei für den Massenimport von Daten

In diesem Thema wird die Verwendung einer Formatdatei bei Massenimportvorgängen beschrieben. Durch die Formatdatei werden die Felder der Datendatei den Spalten der Tabelle zugeordnet. In SQL Server 2005 und höheren Versionen können Sie eine Nicht-XML- oder eine XML-Formatdatei für den Massenimport von Daten verwenden, wenn Sie dazu einen bcp-Befehl bzw. einen BULK INSERT- oder INSERT ... SELECT * FROM OPENROWSET(BULK...)- Transact-SQL-Befehl verwenden.

Wichtiger HinweisWichtig

Damit eine Formatdatei mit einer Datendatei mit Unicode-Zeichen verwendet werden kann, müssen alle Eingabefelder Unicode-Textzeichenfolgen sein (entweder Unicode-Zeichenfolgen einer festen Länge oder Unicode-Zeichenfolgen mit Abschlusszeichen).
HinweisHinweis

Wenn Sie keine Vorkenntnisse zu Formatdateien haben, können Sie sich unter Grundlegendes zu Nicht-XML-Formatdateien und Grundlegendes zu XML-Formatdateien informieren.

Formatdateioptionen für Massenimportbefehle

In der folgenden Tabelle sind die Formatdateioptionen für die einzelnen Massenimportbefehle zusammengefasst.

Massenladebefehle

Verwenden der Formatdateioption

BULK INSERT

FORMATFILE = 'format_file_path'

INSERT ... SELECT * FROM OPENROWSET(BULK...)

FORMATFILE = 'format_file_path'

bcp … in

-fformat_file

Weitere Informationen finden Sie unter bcp (Hilfsprogramm), BULK INSERT (Transact-SQL) oder OPENROWSET (Transact-SQL).

HinweisHinweis

Verwenden Sie in Ihrer Formatdatei einen der folgenden Datentypen, um einen Massenexport oder -import von SQLXML-Daten auszuführen: SQLCHAR oder SQLVARYCHAR (die Daten werden in der Clientcodepage oder in der Codepage, die durch die Sortierung impliziert wird, gesendet), SQLNCHAR oder SQLNVARCHAR (die Daten werden als Unicode gesendet) oder SQLBINARY oder SQLVARYBIN (die Daten werden ohne Konvertierung gesendet).

Beispiele

Die Beispiele in diesem Abschnitt zeigen, wie Formatdateien mit dem bcp-Befehl bzw. den Anweisungen BULK INSERT und INSERT ... SELECT * FROM OPENROWSET(BULK...)- Anweisungen. Bevor Sie eines der Beispiele für den Massenimport nachvollziehen können, müssen Sie eine entsprechende Tabelle, eine Datendatei und eine Formatdatei für das Beispiel erstellen.

Beispieltabelle

Damit die Beispiele nachvollzogen werden können, muss im dbo-Schema in der AdventureWorks2008R2-Beispieldatenbank eine Tabelle mit der Bezeichnung "myTestFormatFiles" erstellt werden. Führen Sie Folgendes aus, um diese Tabelle im SQL Server Management Studio-Abfrage-Editor zu erstellen:

USE AdventureWorks2008R2;
GO
CREATE TABLE myTestFormatFiles (
   Col1 smallint,
   Col2 nvarchar(50),
   Col3 nvarchar(50),
   Col4 nvarchar(50)
   );
GO

Beispieldatendatei

Für die Beispiele wird eine Beispieldatendatei myTestFormatFiles-c.Dat verwendet, die die folgenden Datensätze enthält. Um die Datendatei zu erstellen, geben Sie an der Microsoft Windows-Eingabeaufforderung Folgendes ein:

10,Field2,Field3,Field4
15,Field2,Field3,Field4
46,Field2,Field3,Field4
58,Field2,Field3,Field4

Beispielformatdateien

Bei einigen Beispielen in diesem Abschnitt werden XML-Formatdateien, myTestFormatFiles-f-x-c.Xml, verwendet, während in anderen Beispielen Dateien in einem anderen Format verwendet werden. Beide Formatdateitypen verwenden Zeichendatenformate und ein Feldabschlusszeichen (,), das nicht dem Standard entspricht.

Beispieldatei – Nicht im XML-Format

Im folgenden Beispiel wird zur Generierung einer XML-Formatdatei aus der myTestFormatFiles-Tabelle der bcp-Befehl verwendet. Die Datei myTestFormatFiles.Fmt enthält die folgenden Informationen:

9.0
4
1       SQLCHAR       0       7       ","      1     Col1         ""
2       SQLCHAR       0       100     ","      2     Col2         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      3     Col3         SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       100     "\r\n"   4     Col4         SQL_Latin1_General_CP1_CI_AS

Wenn zur Erstellung dieser Formatdatei der bcp-Befehl mit der Option format verwendet werden soll, geben Sie an der Windows-Eingabeaufforderung Folgendes ein:

bcp AdventureWorks2008R2..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T

Weitere Informationen zum Erstellen einer Formatdatei finden Sie unter Erstellen einer Formatdatei.

Beispieldatei – Im XML-Format

Im folgenden Beispiel wird zur Erstellung einer XML-Formatdatei aus der myTestFormatFiles-Tabelle der bcp-Befehl verwendet. Die Datei myTestFormatFiles.Xml enthält die folgenden Informationen:

<?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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Wenn zur Erstellung dieser Formatdatei der bcp-Befehl mit der Option format verwendet werden soll, geben Sie an der Windows-Eingabeaufforderung Folgendes ein:

bcp AdventureWorks2008R2..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T

Verwenden von bcp

Im nachfolgenden Beispiel wird bcp für den Massenimport von Daten aus der myTestFormatFiles-c.Dat-Datendatei in die HumanResources.myTestFormatFiles-Tabelle in der AdventureWorks2008R2-Beispieldatenbank verwendet. In diesem Beispiel wird die XML-Formatdatei MyTestFormatFiles.Xml verwendet. Vorhandene Tabellenzeilen werden in diesem Beispiel vor dem Import der Datendatei gelöscht.

Geben Sie an der Windows-Eingabeaufforderung Folgendes ein:

bcp AdventureWorks2008R2..myTestFormatFiles in C:\myTestFormatFiles-c.Dat -f C:\myTestFormatFiles.Xml -T
HinweisHinweis

Weitere Informationen zu diesem Befehl finden Sie unter bcp (Hilfsprogramm).

Verwenden von BULK INSERT

Im nachfolgenden Beispiel wird BULK INSERT für den Massenimport von Daten aus der myTestFormatFiles-c.Dat-Datendatei in die HumanResources.myTestFormatFiles-Tabelle in der AdventureWorks2008R2-Beispieldatenbank verwendet. Im Beispiel wird die XML-Formatdatei MyTestFormatFiles.Fmt verwendet. Vorhandene Tabellenzeilen werden in diesem Beispiel vor dem Import der Datendatei gelöscht.

Führen Sie im SQL Server Management Studio-Abfrage-Editor folgende Anweisung aus:

USE AdventureWorks2008R2;
GO
DELETE myTestFormatFiles;
GO
BULK INSERT myTestFormatFiles 
   FROM 'C:\myTestFormatFiles-c.Dat' 
   WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt');
GO
SELECT * FROM myTestFormatFiles;
GO
HinweisHinweis

Weitere Informationen zu dieser Anweisung finden Sie unter BULK INSERT (Transact-SQL).

Verwenden des OPENROWSET-Bulk-Rowsetanbieters

Im nachfolgenden Beispiel wird INSERT ... SELECT * FROM OPENROWSET(BULK...) für den Massenimport von Daten aus der myTestFormatFiles-c.Dat-Datendatei in die HumanResources.myTestFormatFiles-Tabelle in der AdventureWorks2008R2-Beispieldatenbank verwendet. In diesem Beispiel wird die XML-Formatdatei MyTestFormatFiles.Xml verwendet. Vorhandene Tabellenzeilen werden in diesem Beispiel vor dem Import der Datendatei gelöscht.

Führen Sie im SQL Server Management Studio-Abfrage-Editor folgende Anweisung aus:

USE AdventureWorks2008R2;
DELETE myTestFormatFiles;
GO
INSERT INTO myTestFormatFiles
    SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestFormatFiles-c.Dat',
      FORMATFILE='C:\myTestFormatFiles.Xml'     
      ) as t1 ;
GO
SELECT * FROM myTestFormatFiles;
GO

Wenn Sie die Beispieltabelle nicht mehr benötigen, können Sie sie mit der folgenden Anweisung löschen:

DROP TABLE myTestFormatFiles
HinweisHinweis

Weitere Informationen zur OPENROWSET BULK-Klausel finden Sie unter OPENROWSET (Transact-SQL).