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

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics 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) .

Gliederung
Beibehalten von Identitätswerten
Beispieltestbedingungen
 ● Beispieltabelle
 ● Beispieldatendatei
 ● Beispiel einer Nicht-XML-Formatdatei
Beispiele
 ● Verwenden von bcp und Beibehalten der Identitätswerte ohne Formatdatei
 ● Verwenden von bcp und Beibehalten der Identitätswerte mit einer Nicht-XML-Formatdatei
 ● Verwenden von bcp und generierten Identitätswerten ohne Formatdatei
 ● Verwenden von bcp und generierten Identitätswerten mit einer Nicht-XML-Formatdatei
 ● Verwenden von BULK INSERT und Beibehalten der Identitätswerte ohne Formatdatei
 ● Verwenden von BULK INSERT und Beibehalten der Identitätswerte mit einer Nicht-XML-Formatdatei
 ● Verwenden von BULK INSERT und generierten Identitätswerten ohne Formatdatei
 ● Verwenden von BULK INSERT und generierten Identitätswerten mit einer Nicht-XML-Formatdatei
 ● Verwenden von OPENROWSET und Beibehalten der Identitätswerte mit einer Nicht-XML-Formatdatei
 ● Verwenden von OPENROWSET und generierten Identitätswerten mit einer Nicht-XML-Formatdatei

Beibehalten von Identitätswerten

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:

Get-Help Qualifizierer zur Identitätsbeibehaltung Qualifizierertyp
bcp E- Schalter
BULK INSERT KEEPIDENTITY Argument
INSERT ... SELECT * FROM OPENROWSET(BULK...) KEEPIDENTITY Tabellenhinweis

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

Hinweis

Weitere Informationen zu einer automatisch inkrementierten Zahl, die in mehreren Tabellen verwendet oder aus Anwendungen aufgerufen werden kann, ohne dass auf eine Tabelle verwiesen wird, finden Sie unter Sequenznummern.

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;

Beispiel einer Nicht-XML-Formatdatei

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. Ausführliche Informationen finden Sie unter 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. Geben Sie bei der Ausführung eines bcp -Befehls zum Erstellen einer Formatdatei das format -Argument an, 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.myIdentity format nul -c -f D:\BCP\myIdentity.fmt -t, -T

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

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

Beispiele

Die nachstehenden Beispiele verwenden die oben erstellte Datenbank, Datendatei und Formatdatei.

Verwenden von bcp und Beibehalten der Identitätswerte ohne Formatdatei

Schalter-E . 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 von bcp und Beibehalten der Identitätswerte mit einer Nicht-XML-Formatdatei

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 von bcp und generierten Identitätswerten ohne 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 von bcp und generierten Identitätswerten 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;"

Verwenden von BULK INSERT und Beibehalten der Identitätswerte ohne Formatdatei

ArgumentKEEPIDENTITY . 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 von BULK INSERT und Beibehalten der Identitätswerte mit einer Nicht-XML-Formatdatei

ArgumenteKEEPIDENTITY 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 von BULK INSERT und generierten Identitätswerten 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 von BULK INSERT und generierten Identitätswerten mit einer Nicht-XML-Formatdatei

Verwenden Sie die 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 der Identitätswerte mit einer Nicht-XML-Formatdatei

TabellenhinweisKEEPIDENTITY und 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
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 von OPENROWSET(BULK...) und generierten Identitätswerten mit einer Nicht-XML-Formatdatei

Verwenden Sie die 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
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;

Related Tasks

So verwenden Sie eine Formatdatei

So verwenden Sie Datenformate für Massenimport oder Massenexport

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

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

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

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

Weitere Informationen

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