Behåll identitetsvärden vid massimport av data (SQL Server)

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Datafiler som innehåller identitetsvärden kan massimporteras till en instans av Microsoft SQL Server.

Som standard ignoreras värdena för identitetskolumnen i datafilen som importeras och SQL Server tilldelar unika värden automatiskt. De unika värdena baseras på de start- och inkrementsvärden som anges när tabellen skapas.

Om datafilen inte innehåller värden för identifierarkolumnen i tabellen använder du en formatfil för att ange att identifierarkolumnen i tabellen ska hoppas över när data importeras. Mer information finns i Använda en formatfil för att hoppa över en tabellkolumn (SQL Server).

Behåll identitetsvärden

Om du vill förhindra att SQL Server tilldelar identitetsvärden vid massimport av datarader till en tabell använder du lämplig behåll-identitet-kommandokvalifikation. När du anger en keep-identity-kvalificerare använder SQL Server identitetsvärdena i datafilen.

Dessa kvalificerare är följande:

Befallning	Behåll identitetsbestämning	Typ av kvalificerare
bcp	-E	Strömbrytare
BULK INSERT	KEEPIDENTITY	Argumentation
INSERT ... SELECT * FROM OPENROWSET(BULK...)	KEEPIDENTITY	Tabellanvisning

Mer information finns i bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET BULK (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL), och Table hints (Transact-SQL).

Not

Information om hur du skapar ett automatiskt inkrementellt tal som kan användas i flera tabeller eller som kan anropas från program utan att referera till någon tabell finns i Sekvensnummer.

Exempel på testvillkor

Exemplen i det här avsnittet baseras på tabellen, datafilen och formatfilen som definieras nedan.

Exempeltabell

Skriptet nedan skapar en testdatabas och en tabell med namnet myIdentity. Kör följande Transact-SQL i 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
   );

Exempeldatafil

Använd Anteckningar, skapa en tom fil D:\BCP\myIdentity.bcp och infoga data nedan.

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

Du kan också köra följande PowerShell-skript för att skapa och fylla i datafilen:

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;

Exempelfil med icke-XML-format

SQL Server stöder två typer av formatfiler: icke-XML-format och XML-format. Icke-XML-format är det ursprungliga format som stöds av tidigare versioner av SQL Server. Mer information finns i Använda filer i format som inte är XML (SQL Server).

Följande kommando använder verktyget bcp för att generera en fil som inte är xml-format, myIdentity.fmt, baserat på schemat för myIdentity.

• Om du vill använda kommandot bcp för att skapa en formatfil anger du argumentet format och använder nul i stället för en datafilsökväg.
• Formatalternativet kräver också alternativet -f.
• c används för att ange teckendata
• t, används för att ange ett kommatecken som en fältavgränsare
• T används för att ange en betrodd anslutning med integrerad säkerhet.

I en kommandotolk anger du följande kommando:

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

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

Viktig

Se till att filer i ett annat format än XML slutar med en vagnretur och radmatning. Annars får du troligen följande felmeddelande:

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

Exempel

Exemplen använder databasen, datafilen och formatfilerna som skapas i den här artikeln.

Använd bcp och behåll identitetsvärden utan en formatfil

Växeln -E .

I en kommandotolk anger du följande kommando:

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

Använd bcp och behåll identitetsvärden med en fil som inte är XML-format

Strömbrytarna -E och -f.

I en kommandotolk anger du följande kommando:

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

Använda bcp- och genererade identitetsvärden utan en formatfil

Använd standardvärden.

I en kommandotolk anger du följande kommando:

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

Använd bcp och genererade identitetsvärden med en icke-XML-formatfil

Använd standardvärden och -f omkopplare.

I en kommandotolk anger du följande kommando:

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

Använd BULK INSERT och behåll identitetsvärden utan en formatfil

Argumentet KEEPIDENTITY.

Kör följande Transact-SQL i 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;

Använd BULK INSERT och behåll identitetsvärden med en fil som inte är XML-format

Argumenten KEEPIDENTITY och FORMATFILE.

Kör följande Transact-SQL i 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;

Använd BULK INSERT och genererade ID-värden utan en formatfil

Använd standardvärden.

Kör följande Transact-SQL i 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;

Använd BULK INSERT och genererade identitetsvärden med en fil i icke-XML-format

Använda standardvärden och FORMATFILE argument.

Kör följande Transact-SQL i 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;

Använd OPENROWSET BULK och behåll identitetsvärden med en fil som inte är XML-format

Tabelltipset KEEPIDENTITY och FORMATFILE argumentet.

Kör följande Transact-SQL i 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;

Använda OPENROWSET BULK och genererade identitetsvärden med en fil som inte är XML-format

Använda standardvärden och FORMATFILE argumentet.

Kör följande Transact-SQL i 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;

Relaterade uppgifter

• Behåll null-värden eller standardvärden under massimport (SQL Server)

• Förbereda data för massexport eller import

Om du vill använda en formatfil

• Skapa en formatfil med bcp (SQL Server)

• Använd en formatfil för att massimportera data (SQL Server)

• Använd en formatfil för att mappa tabellkolumner till datafilfält (SQL Server)

• Använd en formatfil för att hoppa över ett datafält (SQL Server)

• Använd en formatfil för att hoppa över en tabellkolumn (SQL Server)

Om du vill använda dataformat för massimport eller massexport

• Importera interna data och teckenformatdata från tidigare versioner av SQL Server

• Använd teckenformat för att importera eller exportera data (SQL Server)

• Använda internt format för att importera eller exportera data (SQL Server)

• Använd unicode-teckenformat för att importera eller exportera data (SQL Server)

• Använda unicode-inbyggt format för att importera eller exportera data (SQL Server)

Ange dataformat för kompatibilitet när du använder bcp

1. Ange fält- och radavgränsare (SQL Server)

2. Ange prefixlängd i datafiler med bcp (SQL Server)

3. Ange fillagringstyp med bcp (SQL Server)

Relaterat innehåll

• Säkerhetskopiering (Transact-SQL)
• bcp-verktyg
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• Tabelltips (Transact-SQL)
• Formatera filer för att importera eller exportera data (SQL Server)