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

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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.

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:

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

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 met niet-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. Zie Niet-XML-indelingsbestanden (SQL Server) gebruiken voor meer 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, geeft u het argument format op en gebruikt u nul in plaats van een pad naar een gegevensbestand.
• Voor de indelingsoptie is ook de -f optie vereist.
• c wordt gebruikt om tekengegevens op te geven
• t, wordt gebruikt om een komma op te geven als een veldeindteken
• 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

Voorbeelden

In de voorbeelden worden de database-, gegevensbestand- en indelingsbestanden gebruikt die in dit artikel zijn gemaakt.

BCP gebruiken en identiteitswaarden behouden zonder een indelingsbestand

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

BCP gebruiken en identiteitswaarden behouden met een bestand met een niet-XML-indeling

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

BCP en gegenereerde identiteitswaarden gebruiken 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;"

bcp en gegenereerde identiteitswaarden gebruiken met een niet-XML-formaatbestand

Gebruik de standaardinstellingen en -f schakeloptie.

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

BULK INSERT gebruiken en identiteitswaarden behouden zonder een indelingsbestand

Het KEEPIDENTITY argument.

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;

BULK INSERT gebruiken en identiteitswaarden behouden met een niet-XML-indelingsbestand

De KEEPIDENTITY argumenten en de FORMATFILE argumenten.

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;

Gebruik BULK INSERT en maak gebruik van 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;

BULK INSERT en gegenereerde identiteitswaarden gebruiken met een niet-XML-indelingsbestand

Standaardinstellingen en FORMATFILE argumenten 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;

OPENROWSET BULK gebruiken en identiteitswaarden behouden met een niet-XML-formaatbestand

De KEEPIDENTITY tabelhint en FORMATFILE het 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;

OPENROWSET BULK en gegenereerde identiteitswaarden gebruiken met een bestand met een niet-XML-indeling

Standaardinstellingen en het FORMATFILE argument 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

• Null-waarden of standaardwaarden behouden tijdens het bulksgewijs importeren (SQL Server)

• gegevens voorbereiden voor bulksgewijs exporteren of importeren

Een indelingsbestand gebruiken

• Een indelingsbestand maken met bcp (SQL Server)

• Een indelingsbestand gebruiken om gegevens bulksgewijs te importeren (SQL Server)

• Een indelingsbestand gebruiken om tabelkolommen toe te wijzen aan gegevensbestandsvelden (SQL Server)

• Een indelingsbestand gebruiken om een gegevensveld (SQL Server) over te slaan

• Een indelingsbestand gebruiken om een tabelkolom (SQL Server) over te slaan

Gegevensindelingen gebruiken voor bulkimport of bulkexport

• Gegevens in systeemeigen en tekenindeling importeren uit eerdere versies van SQL Server

• Tekenindeling gebruiken om gegevens (SQL Server) te importeren of te exporteren

• Systeemeigen indeling gebruiken voor het importeren of exporteren van gegevens (SQL Server)

• Unicode-tekenindeling gebruiken om gegevens (SQL Server) te importeren of te exporteren

• Unicode Native Format gebruiken voor het importeren of exporteren van gegevens (SQL Server)

Gegevensindelingen opgeven voor compatibiliteit bij het gebruik van bcp-

1. veld- en rijeindtekens (SQL Server) opgeven

2. Lengte van voorvoegsel opgeven in gegevensbestanden met behulp van bcp (SQL Server)

3. Bestandstype opgeven met bcp (SQL Server)

Verwante inhoud

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