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

Kontur

Behåll identitetsvärden Exempel på testvillkor  ● Exempeltabell  ● exempeldatafil  ● exempelfil i icke-XML-format Exempel  ● Använda bcp och behålla identitetsvärden utan en formatfil  ● Använda bcp och behålla identitetsvärden med en fil som inte är XML-format  ● Använda bcp- och genererade identitetsvärden utan en formatfil  ● Använda bcp- och genererade identitetsvärden med en fil som inte är XML-format  ● Använda BULK INSERT och behålla identitetsvärdena utan en formatfil  ● Använda BULK INSERT och behålla identitetsvärden med en fil som inte är i XML-format  ● Använda BULK INSERT och genererade identitetsvärden utan formatfil  ● Använda MASSINFOGNING och genererade identitetsvärden med ett icke-XML-formatfil  ● Använda OPENROWSET och behålla identitetsvärden med en fil med icke-XML-format  ● Använda OPENROWSET och genererade identitetsvärden med en icke-XML formatfil

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-införsel	KEEPIDENTITY	Argument
INFOGA... VÄLJ * FRÅN OPENROWSET(BULK...)	KEEPIDENTITY	Tabellanvisning

Mer information finns i bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET (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 som inte är 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. Granska (SQL Server) icke-XML-formatfiler för detaljerad information. 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. I det här exemplet används dessutom c för att ange teckeninformation, och t, används för att ange ett kommatecken som en fältavslutare, och T- används för att specificera 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

I exemplen nedan används databasen, datafilen och formatfilerna som skapades ovan.

Använda bcp och behålla identitetsvärden utan en formatfil

-E brytare. 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ända bcp och behålla identitetsvärden med en fil som inte är XML-format

-E och -f växlar. 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

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ända bcp- och genererade identitetsvärden med en fil som inte är XML-format

Använd standardvärden och -f switch. 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ända BULK INSERT och bibehålla identitetsvärden utan

KEEPIDENTITY 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 (
		DATAFILETYPE = 'char',  
		FIELDTERMINATOR = ',',  
		KEEPIDENTITY
		);

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

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

KEEPIDENTITY och argumentet 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ända BULK INSERT- och genererade identitetsvärden utan

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ända BULK INSERT- och genererade identitetsvärden med en fil som inte är XML-format

Använd 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ända OPENROWSET(BULK...) och behålla identitetsvärden med en fil som inte är XML-format

KEEPIDENTITY tabellhint 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
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änd 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
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ålla nullvärden eller använda standardvärden under massimport (SQL Server)

• Förbereda data för massexport eller import (SQL Server)

Om du vill använda en formatfil

• Skapa en formatfil (SQL Server)

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

• Använda en formatfil för att mappa tabellkolumner till Data-File fält (SQL Server)

• Använda 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 inhemska och teckenformatsdata från tidigare versioner av SQL Server

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

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

• använda 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 hjälp av bcp (SQL Server)

3. Ange lagringstyp för filer med hjälp av bcp (SQL Server)

Se även

BACKUP (Transact-SQL)
bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
tabelltips (Transact-SQL)
formatera filer för att importera eller exportera data (SQL Server)