Mantenere i valori Identity durante l'importazione bulk dei dati (SQL Server)Keep Identity Values When Bulk Importing Data (SQL Server)

È possibile eseguire l'importazione bulk di file di dati contenenti valori Identity in un'istanza di Microsoft SQL Server.Data files that contain identity values can be bulk imported into an instance of Microsoft SQL Server. Per impostazione predefinita, i valori per la colonna Identity del file di dati importato vengono ignorati e sostituiti automaticamente da valori univoci assegnati da SQL ServerSQL Server .By default, the values for the identity column in the data file that is imported are ignored and SQL ServerSQL Server assigns unique values automatically. I valori univoci si basano sui valori di inizializzazione e incremento specificati durante la creazione della tabella.The unique values are based on the seed and increment values that are specified during table creation.

Se il file di dati non contiene valori per la colonna dell'identificatore nella tabella, utilizzare un file di formato per specificare di ignorare la colonna dell'identificatore nella tabella durante l'importazione dei dati.If the data file does not contain values for the identifier column in the table, use a format file to specify that the identifier column in the table should be skipped when importing data. Per altre informazioni, vedere Utilizzo di un file di formato per ignorare una colonna di una tabella (SQL Server) .See Use a Format File to Skip a Table Column (SQL Server) for additional information.

RiquadroOutline
Mantenere i valori IdentityKeep Identity Values
Condizioni di test di esempioExample Test Conditions
 ● Tabella di esempio ● Sample Table
 ● File di dati di esempio ● Sample Data File
 ● File di formato non XML di esempio ● Sample Non-XML Format File
EsempiExamples
 ● Uso di bcp e mantenimento dei valori Identity senza un file di formato ● Using bcp and Keeping Identity Values without a Format File
 ● Uso di bcp e mantenimento dei valori Identity con un file di formato non XML ● Using bcp and Keeping Identity Values with a Non-XML Format File
 ● Uso di bcp e dei valori Identity generati senza un file di formato ● Using bcp and Generated Identity Values without a Format File
 ● Uso di bcp e dei valori Identity generati con un file di formato non XML ● Using bcp and Generated Identity Values with a Non-XML Format File
 ● Uso di BULK INSERT e mantenimento dei valori Identity senza un file di formato ● Using BULK INSERT and Keeping Identity Values without a Format File
 ● Uso di BULK INSERT e mantenimento dei valori Identity con un file di formato non XML ● Using BULK INSERT and Keeping Identity Values with a Non-XML Format File
 ● Uso di BULK INSERT e dei valori Identity generati senza un file di formato ● Using BULK INSERT and Generated Identity Values without a Format File
 ● Uso di BULK INSERT e dei valori Identity generati con un file di formato non XML ● Using BULK INSERT and Generated Identity Values with a Non-XML Format File
 ● Uso di OPENROWSET e mantenimento dei valori Identity con un file di formato non XML ● Using OPENROWSET and Keeping Identity Values with a Non-XML Format File
 ● Uso di OPENROWSET e dei valori Identity generati con un file di formato non XML ● Using OPENROWSET and Generated Identity Values with a Non-XML Format File

Mantenere i valori Identity Keep Identity Values

Per impedire l'assegnazione da parte di SQL ServerSQL Server di valori Identity durante l'importazione bulk delle righe di dati in una tabella, utilizzare il qualificatore per il mantenimento dei valori Identity corretto.To prevent SQL ServerSQL Server from assigning identity values while bulk importing data rows into a table, use the appropriate keep-identity command qualifier. Quando si specifica un qualificatore per il mantenimento dei valori Identity, SQL ServerSQL Server utilizza i valori Identity nel file di dati.When you specify a keep-identity qualifier, SQL ServerSQL Server uses the identity values in the data file. Sono disponibili i qualificatori seguenti:These qualifiers are as follows:

CommandCommand Qualificatore per il mantenimento dei valori IdentityKeep-identity qualifier Tipo di qualificatoreQualifier type
bcpbcp -E-E OpzioneSwitch
BULK INSERTBULK INSERT KEEPIDENTITYKEEPIDENTITY ArgomentoArgument
INSERT ... SELECT * FROM OPENROWSET(BULK...).INSERT ... SELECT * FROM OPENROWSET(BULK...) KEEPIDENTITYKEEPIDENTITY Hint di tabellaTable hint

Per altre informazioni, vedere Utilità bcp, BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL) e Hint di tabella (Transact-SQL).For more information, see bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL), and Table Hints (Transact-SQL).

Nota

Per creare un numero a incremento automatico da usare in più tabelle o da chiamare dalle applicazioni senza fare riferimento ad alcuna tabella, vedere Numeri di sequenza.To create an automatically incrementing number that can be used in multiple tables or that can be called from applications without referencing any table, see Sequence Numbers.

Condizioni di test di esempioExample Test Conditions

Gli esempi riportati in questo argomento sono basati sulla tabella, il file di dati e il file di formato definiti di seguito.The examples in this topic are based on the table, data file, and format file defined below.

Tabella di esempioSample Table

Lo script seguente crea un database di prova e una tabella denominata myIdentity.The script below creates a test database and a table named myIdentity. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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
   );

File di dati di esempioSample Data File

Usando il Blocco note, creare un file D:\BCP\myIdentity.bcp vuoto e inserire i dati seguenti.Using Notepad, create an empty file D:\BCP\myIdentity.bcp and insert the data below.

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

In alternativa, è possibile eseguire lo script PowerShell seguente per creare e popolare il file di dati:Alternatively, you can execute the following PowerShell script to create and populate the data file:

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;

File di formato non XML di esempioSample Non-XML Format File

SQL Server supporta due tipi di file di formato, ovvero non XML e XML.SQL Server support two types of format file: non-XML format and XML format. Il formato non XML è il formato originale supportato dalle versioni precedenti di SQL Server.The non-XML format is the original format that is supported by earlier versions of SQL Server. Per informazioni dettagliate, vedere File in formato non XML (SQL Server) .Please review Non-XML Format Files (SQL Server) for detailed information. Il comando seguente userà l' utility bcp per generare un formato di file non XML, myIdentity.fmt, sulla base dello schema di myIdentity.The following command will use the bcp utility to generate a non-xml format file, myIdentity.fmt, based on the schema of myIdentity. Per usare un comando bcp per creare un file di formato, specificare l'argomento format e usare nul anziché un percorso del file di dati.To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. L'opzione format richiede anche l'opzione -f .The format option also requires the -f option. Inoltre, in questo esempio il qualificatore c viene usato per specificare dati di tipo carattere, t, viene usato per specificare la virgola come carattere di terminazione del campoe T viene usato per specificare una connessione trusted che usa la sicurezza integrata.In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. Al prompt dei comandi immettere il comando seguente:At a command prompt, enter the following command:

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

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

Importante

Verificare che il file di formato non XML termini con un ritorno a capo/avanzamento riga.Ensure your non-XML format file ends with a carriage return\line feed. In caso contrario, è possibile che venga visualizzato il messaggio di errore seguente:Otherwise you will likely receive the following error message:

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

EsempiExamples

Gli esempi seguenti usano il database, il file di dati e i file di formato creati in precedenza.The examples below use the database, datafile, and format files created above.

Uso di bcp e mantenimento dei valori Identity senza un file di formatoUsing bcp and Keeping Identity Values without a Format File

Opzione-E .-E switch. Al prompt dei comandi immettere il comando seguente:At a command prompt, enter the following command:

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

Uso di bcp e mantenimento dei valori Identity con un file di formato non XMLUsing bcp and Keeping Identity Values with a Non-XML Format File

Opzioni-E e -f .-E and -f switches. Al prompt dei comandi immettere il comando seguente:At a command prompt, enter the following command:

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

Uso di bcp e dei valori Identity generati senza un file di formatoUsing bcp and Generated Identity Values without a Format File

Uso dei valori predefiniti.Using defaults. Al prompt dei comandi immettere il comando seguente:At a command prompt, enter the following command:

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

Uso di bcp e dei valori Identity generati con un file di formato non XMLUsing bcp and Generated Identity Values with a Non-XML Format File

Uso dei valori predefiniti e dell'opzione -f .Using defaults and -f switch. Al prompt dei comandi immettere il comando seguente:At a command prompt, enter the following command:

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

Uso di BULK INSERT e mantenimento dei valori Identity senza un file di formatoUsing BULK INSERT and Keeping Identity Values without a Format File

ArgomentoKEEPIDENTITY .KEEPIDENTITY argument. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Uso di BULK INSERT e mantenimento dei valori Identity con un file di formato non XMLUsing BULK INSERT and Keeping Identity Values with a Non-XML Format File

ArgomentiKEEPIDENTITY e FORMATFILE .KEEPIDENTITY and the FORMATFILE argument. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Uso di BULK INSERT e dei valori Identity generati senza un file di formatoUsing BULK INSERT and Generated Identity Values without a Format File

Uso dei valori predefiniti.Using defaults. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Uso di BULK INSERT e dei valori Identity generati con un file di formato non XMLUsing BULK INSERT and Generated Identity Values with a Non-XML Format File

Uso dei valori predefiniti e dell'argomento FORMATFILE .Using defaults and FORMATFILE argument. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Uso di OPENROWSET(BULK...) e mantenimento dei valori Identity con un file di formato non XMLUsing OPENROWSET(BULK...) and Keeping Identity Values with a Non-XML Format File

Hint di tabellaKEEPIDENTITY e argomento FORMATFILE .KEEPIDENTITY table hint and FORMATFILE argument. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Uso di OPENROWSET(BULK...) e dei valori Identity generati con un file di formato non XMLUsing OPENROWSET(BULK...) and Generated Identity Values with a Non-XML Format File

Uso dei valori predefiniti e dell'argomento FORMATFILE .Using defaults and FORMATFILE argument. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;
  1. Specificare caratteri di terminazione del campo e della riga (SQL Server)Specify Field and Row Terminators (SQL Server)

  2. Specificare la lunghezza del prefisso nei file di dati con bcp (SQL Server)Specify Prefix Length in Data Files by Using bcp (SQL Server)

  3. Specificare il tipo di archiviazione di file con bcp (SQL Server)Specify File Storage Type by Using bcp (SQL Server)

Vedere ancheSee Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
Utilità bcp bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
Hint di tabella (Transact-SQL)Table Hints (Transact-SQL)
File di formato per l'importazione o l'esportazione di dati (SQL Server)Format Files for Importing or Exporting Data (SQL Server)