Freigeben über

Massenimport von Daten mithilfe einer Formatdatei (SQL Server)

Gilt für:SQL ServerAzure SQL DatabaseAzure 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 mit bcp (SQL Server ).

Voraussetzungen

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.

Ändern Sie die lokalen Dateispeicherorte des Codebeispiels in einen Dateispeicherort auf Ihrem Computer.

Erstellen Sie eine Nicht-XML-Formatdatei

Ausführliche Informationen finden Sie unter Verwendung von 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.

  • Wenn Sie einen bcp-Befehl zum Erstellen einer Formatdatei verwenden möchten, geben Sie das format Argument an, und verwenden Sie nul das Argument anstelle eines Datendateipfads.
  • Die Formatoption erfordert auch die -f Option.
  • c wird verwendet, um Zeichendaten anzugeben.
  • t,wird verwendet, um ein Komma als Feldterminator anzugeben.
  • T wird verwendet, um eine vertrauenswürdige Verbindung mit integrierter Sicherheit anzugeben.

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

Überprüfen Sie XML-Formatdateien (SQL Server) auf ausführliche Informationen. Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die XML-Formatdatei myFirstImport.xmlzu erstellen, die auf dem Schema von myFirstImportbasiert.

  • Wenn Sie einen bcp-Befehl zum Erstellen einer Formatdatei verwenden möchten, geben Sie das format Argument an, und verwenden Sie nul das Argument anstelle eines Datendateipfads.
  • Für die Formatoption ist immer die -f Option erforderlich.
  • Zum Erstellen einer XML-Formatdatei müssen Sie auch die -x Option angeben.
  • c wird verwendet, um Zeichendaten anzugeben.
  • t,wird verwendet, um ein Komma als Feldterminator anzugeben.
  • T wird verwendet, um eine vertrauenswürdige Verbindung mit integrierter Sicherheit anzugeben.

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 von Bcp - und Nicht-XML-Formatdateien (SQL Server)

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 von bcp- und XML-Formatdateien (SQL Server)

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 von BULK INSERT (Transact-SQL) und Verwenden von Nicht-XML-Formatdateien (SQL Server)

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 von BULK INSERT (Transact-SQL) und XML-Formatdateien (SQL Server)

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 von OPENROWSET BULK (Transact-SQL) und Verwenden von Nicht-XML-Formatdateien (SQL Server)

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 von OPENROWSET BULK (Transact-SQL) und XML-Formatdateien (SQL Server)

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

Verwandte Inhalte