Identiteitswaarden behouden bij het bulksgewijs importeren van gegevens (SQL Server)

Artikel
03/05/2025

van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Gegevensbestanden die identiteitswaarden bevatten, kunnen bulksgewijs worden geïmporteerd in een exemplaar van Microsoft SQL Server. De waarden voor de identiteitskolom in het geïmporteerde gegevensbestand worden standaard genegeerd en SQL Server wijst automatisch unieke waarden toe. De unieke waarden zijn gebaseerd op de seed- en incrementele waarden die worden opgegeven tijdens het maken van de tabel.

Als het gegevensbestand geen waarden voor de id-kolom in de tabel bevat, gebruikt u een indelingsbestand om op te geven dat de id-kolom in de tabel moet worden overgeslagen bij het importeren van gegevens. Zie Een indelingsbestand gebruiken om een tabelkolom (SQL Server) over te slaan voor meer informatie.

Overzicht
Identiteitswaarden behouden voorbeeldtestvoorwaarden ● Voorbeeldtabel ● een voorbeeldgegevensbestand ● voorbeeldbestand zonder XML-indeling voorbeelden ● bcp gebruiken en identiteitswaarden behouden zonder een formaatbestand ● BCP gebruiken en identiteitswaarden behouden met een niet-XML-indelingsbestand ● Het gebruik van bcp en gegenereerde identiteitswaarden zonder een indelingsbestand ● Het gebruik van bcp en gegenereerde identiteitswaarden met een niet-XML-indelingsbestand ● Door BULK INSERT te gebruiken en identiteitswaarden te behouden zonder een formatbestand ● BULK INSERT gebruiken en identiteitswaarden behouden met een niet-XML-bestandsformaat ● BULK INSERT en de gegenereerde identiteitswaarden gebruiken zonder een opmaakbestand ● BULK INSERT en gegenereerde identiteitswaarden gebruiken met een niet-XML-formaatbestand ● OPENROWSET gebruiken en identity waarden behouden met een niet-XML-indelingsbestand ● Gebruik van OPENROWSET en gegenereerde identiteitswaarden met een bestandsindeling die geen XML is

Overzicht

Identiteitswaarden behouden
voorbeeldtestvoorwaarden
● Voorbeeldtabel
● een voorbeeldgegevensbestand
● voorbeeldbestand zonder XML-indeling
voorbeelden
● bcp gebruiken en identiteitswaarden behouden zonder een formaatbestand
● BCP gebruiken en identiteitswaarden behouden met een niet-XML-indelingsbestand
● Het gebruik van bcp en gegenereerde identiteitswaarden zonder een indelingsbestand
● Het gebruik van bcp en gegenereerde identiteitswaarden met een niet-XML-indelingsbestand
● Door BULK INSERT te gebruiken en identiteitswaarden te behouden zonder een formatbestand
● BULK INSERT gebruiken en identiteitswaarden behouden met een niet-XML-bestandsformaat
● BULK INSERT en de gegenereerde identiteitswaarden gebruiken zonder een opmaakbestand
● BULK INSERT en gegenereerde identiteitswaarden gebruiken met een niet-XML-formaatbestand
● OPENROWSET gebruiken en identity waarden behouden met een niet-XML-indelingsbestand
● Gebruik van OPENROWSET en gegenereerde identiteitswaarden met een bestandsindeling die geen XML is

Identiteitswaarden behouden

Als u wilt voorkomen dat SQL Server identiteitswaarden toewijst tijdens het bulksgewijs importeren van gegevensrijen in een tabel, gebruikt u de juiste kwalificatie voor keep-identity-opdrachten. Wanneer u een kwalificatie voor keep-identity opgeeft, gebruikt SQL Server de identiteitswaarden in het gegevensbestand. Deze kwalificaties zijn als volgt:

Bevelen	Kwalificatie voor keep-identity	Kwalificatietype
bcp	-E	Schakelaar
BULK INSERT	KEEPIDENTITY	Discussie
INVOEGEN... SELECT * FROM OPENROWSET(BULK...)	KEEPIDENTITY	Tabelhint

Zie bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL)en tabelhints (Transact-SQL)voor meer informatie.

Notitie

Zie Reeksnummersals u een automatisch oplopend getal wilt maken dat kan worden gebruikt in meerdere tabellen of die vanuit toepassingen kunnen worden aangeroepen zonder naar een tabel te verwijzen.

Voorbeeld van testvoorwaarden

De voorbeelden in dit onderwerp zijn gebaseerd op de tabel, het gegevensbestand en het indelingsbestand dat hieronder is gedefinieerd.

voorbeeldtabel

Met het onderstaande script maakt u een testdatabase en een tabel met de naam myIdentity. Voer de volgende Transact-SQL uit in Microsoft SQL Server Management Studio (SSMS):

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
   );

voorbeeldgegevensbestand

Maak met Kladblok een leeg bestand D:\BCP\myIdentity.bcp en voeg de onderstaande gegevens in.

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

U kunt ook het volgende PowerShell-script uitvoeren om het gegevensbestand te maken en te vullen:

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;

voorbeeldbestand zonder XML-indeling

SQL Server ondersteunt twee typen indelingsbestand: niet-XML-indeling en XML-indeling. De niet-XML-indeling is de oorspronkelijke indeling die wordt ondersteund door eerdere versies van SQL Server. Raadpleeg niet-XML-bestandsformaten (SQL Server) voor gedetailleerde informatie. Met de volgende opdracht wordt het hulpprogramma bcp gebruikt om een bestand met een niet-XML-indeling te genereren, myIdentity.fmt, op basis van het schema van myIdentity. Als u een bcp-opdracht wilt gebruiken om een indelingsbestand te maken, specificeer dan het indelings- argument en gebruik nul in plaats van een gegevensbestandspad. Voor de indelingsoptie is ook de optie -f vereist. Bovendien wordt in dit voorbeeld de kwalificatie c gebruikt voor het opgeven van tekengegevens, t, wordt gebruikt om een komma op te geven als een veldeindtekenen T- wordt gebruikt om een vertrouwde verbinding op te geven met geïntegreerde beveiliging. Voer bij een opdrachtprompt de volgende opdracht in:

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

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

Belangrijk

Zorg ervoor dat uw niet-XML-bestand eindigt met een carriage return\line feed. Anders krijgt u waarschijnlijk het volgende foutbericht:

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

Voorbeelden

In de onderstaande voorbeelden worden de hierboven gemaakte database-, gegevensbestand- en indelingsbestanden gebruikt.

Gebruik van bcp en het behouden van identiteitswaarden zonder een opmaakbestand

-E schakelaar. Voer bij een opdrachtprompt de volgende opdracht in:

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;"

Gebruik van bcp en het behouden van identiteitswaarden met een bestand in niet-XML-indeling

-E en -f schakelaars. Voer bij een opdrachtprompt de volgende opdracht in:

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;"

Met behulp van bcp en gegenereerde identiteitswaarden zonder een indelingsbestand

Standaardwaarden gebruiken. Voer bij een opdrachtprompt de volgende opdracht in:

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;"

Met behulp van bcp en gegenereerde identiteitswaarden met een niet-XML-bestand

Standaardinstellingen en -f switch gebruiken. Voer bij een opdrachtprompt de volgende opdracht in:

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;"

Gebruik van BULK INSERT en het behouden van identiteitswaarden zonder een

argument KEEPIDENTITY. Voer de volgende Transact-SQL uit in Microsoft SQL Server Management Studio (SSMS):

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;

Gebruik van BULK INSERT en het behouden van identiteitswaarden met een niet-XML-indeling

KEEPIDENTITY- en het argument FORMATFILE. Voer de volgende Transact-SQL uit in Microsoft SQL Server Management Studio (SSMS):

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;

Met BULK INSERT en gegenereerde identiteitswaarden zonder een indelingsbestand

Standaardwaarden gebruiken. Voer de volgende Transact-SQL uit in Microsoft SQL Server Management Studio (SSMS):

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;

Met BULK INSERT en Gegenereerde identiteitswaarden gebruiken met een bestand met niet-XML-indeling

Standaardinstellingen en het argument FORMATFILE gebruiken. Voer de volgende Transact-SQL uit in Microsoft SQL Server Management Studio (SSMS):

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;

Met OPENROWSET(BULK...) en het behouden van identiteitswaarden met een niet-XML-indeling bestand

KEEPIDENTITY tabelhint en FORMATFILE argument. Voer de volgende Transact-SQL uit in Microsoft SQL Server Management Studio (SSMS):

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;

Met behulp van OPENROWSET(BULK...) en gegenereerde identiteitswaarden met een bestand met niet-XML-indeling

De standaardinstellingen en het argument FORMATFILE gebruiken. Voer de volgende Transact-SQL uit in Microsoft SQL Server Management Studio (SSMS):

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;

Gerelateerde taken

Een indelingsbestand gebruiken

Gegevensindelingen gebruiken voor bulkimport of bulkexport

Gegevensindelingen opgeven voor compatibiliteit bij het gebruik van bcp-

Zie ook

BACKUP (Transact-SQL)
bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Tabelhints (Transact-SQL)
bestanden opmaken voor het importeren of exporteren van gegevens (SQL Server)

Delen via