Massenimport von Daten mithilfe einer Formatdatei (SQL Server)

  • Artikel

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

In diesem Artikel wird die Verwendung einer Formatdatei bei Massenimportvorgängen beschrieben. Eine Formatdatei ordnet die Felder der Datendatei den Spalten einer Tabelle zu. Weitere Informationen finden Sie unter Erstellen einer Formatdatei (SQL Server).

Voraussetzungen

  • Damit eine Formatdatei mit einer Datendatei mit Unicode-Zeichen verwendet werden kann, müssen alle Eingabefelder Unicode-Textzeichenfolgen enthalten (d.h., entweder Unicode-Zeichenfolgen einer festen Länge oder Unicode-Zeichenfolgen mit Abschlusszeichen).
  • Verwenden Sie in der Formatdatei einen der folgenden Datentypen für den Massenexport oder -import von SQLXML -Daten:
    • SQLCHAR oder SQLVARCHAR (die Daten werden in der Clientcodepage gesendet bzw. in der durch die Sortierung implizierten Codepage)
    • SQLNCHAR oder SQLNVARCHAR (die Daten werden als Unicode gesendet)
    • SQLBINARY oder SQLVARBIN (die Daten werden ohne Konvertierung gesendet)
  • Azure SQL-Datenbank und Azure Synapse Analytics unterstützen nur bcp. Weitere Informationen finden Sie hier:
  • 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 für Formatdateien basieren auf der Tabelle und der Datendatei, die nachstehend definiert sind.

Beispieltabelle

Das folgende Skript erstellt eine Testdatenbank und eine Tabelle namens myFirstImport. Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.MyFirstImport (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );

Beispieldatendatei

Erstellen Sie mit Editor die leere Datei D:\BCP\myFirstImport.bcp , und fügen Sie die folgenden Daten ein:

1,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
3,Stella,Rosenhain,1992-03-02

Alternativ können Sie das folgende PowerShell-Skript ausführen, um die Datei zu erstellen und aufzufüllen:

Clear-Host
# revise directory as desired

$dir = 'D:\BCP\';

$bcpFile = Join-Path -Path $dir -ChildPath 'MyFirstImport.bcp';

# Confirm directory exists

IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# Clear content, will error if file does not exist, can be ignored

Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data

Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,1992-03-02';

# Review content

Get-Content -Path $bcpFile;
Notepad.exe $bcpfile;

Erstellen Sie die Formatdateien

SQL Server unterstützt zwei Typen von Formatdateien: Nicht-XML- und XML-Format. Nicht-XML ist das ursprüngliche Format, das von früheren Versionen von SQL Server unterstützt wird.

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 myFirstImport.fmtzu erstellen, die auf dem Schema von myFirstImportbasiert. Geben Sie das format -Argument an, um einen bcp-Befehl zum Erstellen einer Formatdatei zu verwenden, und verwenden Sie nul anstatt eines Datendateipfads. Die Option „format“ erfordert außerdem die Option -f . Zusätzlich werden folgende Qualifizierer verwendet: c , um Zeichendaten anzugeben, t , um ein Komma als Feldabschlusszeichenanzugeben, und T , um eine vertrauenswürdige Verbindung anzugeben, für die integrierte Sicherheit verwendet wird. Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

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

REM Review file
Notepad D:\BCP\myFirstImport.fmt

Ihre Nicht-XML-Formatdatei D:\BCP\myFirstImport.fmt sollte wie folgt aussehen:

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       11      "\r\n"   4     BirthDate              ""

Wichtig

Stellen Sie sicher, dass Ihre Nicht-XML-Formatdatei mit einem Wagenrücklauf/Zeilenvorschub endet. Andernfalls wird Ihnen möglicherweise die folgende Fehlermeldung angezeigt:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

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 myFirstImport.xmlzu erstellen, die auf dem Schema von myFirstImportbasiert. Geben Sie das format -Argument an, um einen bcp-Befehl zum Erstellen einer Formatdatei zu verwenden, und verwenden Sie nul anstatt eines Datendateipfads. Für die Formatoption ist immer auch die Option -f erforderlich. Zum Erstellen einer XML-Formatdatei muss zudem die Option -x angegeben werden. Zusätzlich werden folgende Qualifizierer verwendet: c , um Zeichendaten anzugeben, t , um ein Komma als Feldabschlusszeichenanzugeben, und T , um eine vertrauenswürdige Verbindung anzugeben, für die integrierte Sicherheit verwendet wird. Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

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

REM Review file
Notepad D:\BCP\myFirstImport.xml

Ihre XML-Formatdatei D:\BCP\myFirstImport.xml sollte wie folgt aussehen:

<?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="11"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
</ROW>
</BCPFORMAT>

Massenimport von Daten mithilfe einer Formatdatei

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:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport"

Verwenden Sie die bcp und die XML-Formatdatei

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.xml -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport;"

Verwenden Sie BULK INSERT und die Nicht-XML-Formatdatei

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

USE TestDatabase;  
GO

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

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

Verwenden Sie von BULK INSERT und die XML-Formatdatei

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

USE TestDatabase;  
GO

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

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

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

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

USE TestDatabase;
GO

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

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

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

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

USE TestDatabase;  
GO

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

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

Weitere Beispiele

Erstellen einer Formatdatei (SQL Server)
Überspringen einer Tabellenspalte mithilfe einer Formatdatei (SQL Server)
Auslassen eines Datenfelds mithilfe einer Formatdatei (SQL Server)
Verwenden einer Formatdatei zum Zuordnen von Tabellenspalten zu Datendateifeldern (SQL Server)

Nächste Schritte