Beibehalten von Identitätswerten beim Massenimport von Daten (SQL Server)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Datendateien, die Identitätswerte enthalten, können per Massenimport in eine Instanz von Microsoft SQL Server übertragen werden.

Standardmäßig werden die Werte für die Identitätsspalte in der importierten Datendatei ignoriert, und von SQL Server werden automatisch eindeutige Werte zugewiesen. Die eindeutigen Werte basieren auf dem Ausgangswert und den inkrementellen Werten, die bei der Tabellenerstellung angegeben werden.

Wenn die Datendatei keine Werte für die Bezeichnerspalte in der Tabelle enthält, sollten Sie eine Formatdatei verwenden, um anzugeben, dass die Bezeichnerspalte beim Importieren von Daten ausgelassen werden soll. Weitere Informationen finden Sie unter Überspringen einer Tabellenspalte mithilfe einer Formatdatei (SQL Server) .

Identitätswerte beibehalten

Um zu verhindern, dass SQL Server beim Massenimport von Datenzeilen in eine Tabelle Identitätswerte zuweist, verwenden Sie den entsprechenden Qualifizierer für den Befehl zur Identitätsbeibehaltung (keep-identity). Wenn Sie einen Qualifizierer zur Identitätsbeibehaltung angeben, verwendet SQL Server die Identitätswerte in der Datendatei.

Nachfolgend sind diese Qualifizierer aufgeführt:

Weitere Informationen finden Sie unter bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET BULK (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL) und Tabellenhinweise (Transact-SQL).

Beispieltestbedingungen

Die in diesem Thema beschriebenen Beispiele basieren auf einer Tabelle, einer Datendatei und einer Formatdatei, die nachstehend definiert werden.

Beispieltabelle

Das folgende Skript erstellt eine Testdatenbank und eine Tabelle namens myIdentity. 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.myIdentity ( 
   PersonID smallint IDENTITY(1,1) NOT NULL,
   FirstName varchar(25) NOT NULL,
   LastName varchar(30) NOT NULL,
   BirthDate date
   );

Beispieldatendatei

Erstellen Sie im Editor die leere Datei D:\BCP\myIdentity.bcp , und fügen Sie die nachstehenden Daten ein.

3,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
1,Stella,Rosenhain,1992-03-02
4,Miller,Dylan,1954-01-05

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

cls
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = $dir + 'myIdentity.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 '3,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '1,Stella,Rosenhain,1992-03-02';
Add-Content -Path $bcpFile -Value '4,Miller,Dylan,1954-01-05';

#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;

Beispieldatei im Nicht-XML-Format

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. Weitere Informationen finden Sie unter Verwenden von Nicht-XML-Formatdateien (SQL Server).

Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die Nicht-XML-Formatdatei myIdentity.fmtzu erstellen, die auf dem Schema von myIdentitybasiert.

• 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.myIdentity format nul -c -f D:\BCP\myIdentity.fmt -t, -T

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

Beispiele

In den Beispielen werden die in diesem Artikel erstellten Datenbank-, Datendatei- und Formatdateien verwendet.

Verwenden von bcp und Beibehalten von Identitätswerten ohne Formatdatei

Der -E Schalter.

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

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

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t, -E

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

Verwenden Sie bcp und behalten Sie Identity-Werte mit einer Datei im Nicht-XML-Format

Die Schalter -E und -f.

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

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

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

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

Verwenden Sie bcp und generierte Identity-Werte ohne eine Formatdatei

Unter Verwendung der Standardeinstellungen.

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

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

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t,

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

Verwenden Sie bcp und generierte Identity-Werte mit einer Nicht-XML-Formatdatei

Verwenden Sie die Standardeinstellungen und den Schalter -f.

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

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

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

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

BULK INSERT verwenden und Identity-Werte ohne Formatdatei beibehalten

Das KEEPIDENTITY Argument.

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

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
    FROM 'D:\BCP\myIdentity.bcp'
    WITH (
        DATAFILETYPE = 'char',  
        FIELDTERMINATOR = ',',  
        KEEPIDENTITY
        );

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

Verwenden Sie BULK INSERT , und behalten Sie IDENTITY-Werte mit einer Nicht-XML-Formatdatei bei

Die Argumente KEEPIDENTITY und FORMATFILE.

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

USE TestDatabase;
GO

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

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

Verwenden Sie BULK INSERT und generierte Identity-Werte ohne Formatdatei

Unter Verwendung der Standardeinstellungen.

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

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = ','
      );

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

Verwenden Sie BULK INSERT und generierte Identitätswerte in Verbindung mit einer Nicht-XML-Formatdatei

Verwenden Sie Standardwerte und das Argument FORMATFILE.

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

USE TestDatabase;
GO

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

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

Verwenden von OPENROWSET BULK und Beibehalten von Identitätswerten mit einer Nicht-XML-Formatdatei

Der KEEPIDENTITY Tabellenhinweis und das FORMATFILE-Argument.

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

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
INSERT INTO dbo.myIdentity
WITH (KEEPIDENTITY) 
(PersonID, FirstName, LastName, BirthDate)
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myIdentity.bcp', 
        FORMATFILE = 'D:\BCP\myIdentity.fmt'  
        ) AS t1;

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

Verwenden Sie OPENROWSET BULK und generierte Identity-Werte mit einer Nicht-XML-Formatdatei

Verwenden von Standardwerten und dem FORMATFILE Argument.

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

USE TestDatabase;
GO

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

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

Verwandte Aufgaben

• Beibehalten von NULL-Werten oder Standardwerten während des Massenimports (SQL Server)

• Vorbereiten von Daten für den Massenexport oder -import

Verwenden einer Formatdatei

• Erstellen einer Formatdatei mit bcp (SQL Server)

• Massenimport von Daten mithilfe einer Formatdatei (SQL Server)

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

• Auslassen eines Datenfelds mithilfe einer Formatdatei (SQL Server)

• Überspringen einer Tabellenspalte mithilfe einer Formatdatei (SQL Server)

So verwenden Sie Datenformate für Massenimport oder Massenexport

• Importieren von systemeigenen und Zeichenformatdaten aus früheren Versionen von SQL Server

• Verwenden des Zeichenformats zum Importieren oder Exportieren von Daten (SQL Server)

• Verwenden des nativen Formats zum Importieren oder Exportieren von Daten (SQL Server)

• Verwenden des Unicode-Zeichenformats zum Importieren und Exportieren von Daten (SQL Server)

• Verwenden des systemeigenen Unicode-Formats zum Importieren oder Exportieren von Daten (SQL Server)

So geben Sie Datenformate für die Kompatibilität bei Verwendung von bcp an

1. Angeben von Feld- und Zeilenendzeichen (SQL Server)

2. Angeben der Präfixlänge in Datendateien mit bcp (SQL Server)

3. Angeben des Dateispeichertyps mithilfe von bcp (SQL Server)

Verwandte Inhalte

• BACKUP (Transact-SQL)
• bcp-Dienstprogramm
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• Tabellenhinweise (Transact-SQL)
• Formatdateien zum Importieren oder Exportieren von Daten (SQL Server)