Massenimport von Daten mithilfe einer Formatdatei (SQL Server)
Gilt für:
SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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 den folgenden Transact-SQL-Befehl 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 den folgenden Transact-SQL-Befehl 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 den folgenden Transact-SQL-Befehl 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 den folgenden Transact-SQL-Befehl 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 den folgenden Transact-SQL-Befehl 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 anzeigen
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
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Tickets als Feedbackmechanismus für Inhalte auslaufen lassen und es durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter: https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für