Mantenere i valori Identity durante l'importazione in blocco dei dati (SQL Server)

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)

È possibile eseguire l'importazione bulk di file di dati contenenti valori Identity in un'istanza di Microsoft SQL Server.

Per impostazione predefinita, i valori per la colonna Identity nel file di dati importato vengono ignorati e sostituiti automaticamente da valori univoci assegnati da SQL Server. I valori univoci si basano sui valori di inizializzazione e incremento specificati durante la creazione della tabella.

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. Per altre informazioni, vedere Utilizzo di un file di formato per ignorare una colonna di una tabella (SQL Server).

Mantenere i valori di identità

Per impedire l'assegnazione da parte di SQL Server di valori Identity durante l'importazione in blocco delle righe di dati in una tabella, utilizzare il qualificatore del comando per il mantenimento dei valori Identity corretto. Quando si specifica un qualificatore per il mantenimento dei valori Identity, SQL Server utilizza i valori Identity nel file di dati.

I qualificatori sono i seguenti:

Comando	Qualificatore per il mantenimento delle identità	Tipo di qualificatore
bcp	-E	Interruttore
BULK INSERT	KEEPIDENTITY	Argomento
INSERT ... SELECT * FROM OPENROWSET(BULK...)	KEEPIDENTITY	Suggerimento per la tabella

Per altre informazioni, vedere Utilità bcp, BULK INSERT (Transact-SQL), OPENROWSET BULK (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL)e Hint di tabella (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.

Condizioni di test di esempio

Gli esempi riportati in questo argomento sono basati sulla tabella, il file di dati e il file di formato definiti di seguito.

Tabella di esempio

Lo script seguente crea un database di prova e una tabella denominata myIdentity. Eseguire l'istruzione Transact-SQL seguente 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
   );

File di dati di esempio

Usando il Blocco note, creare un file D:\BCP\myIdentity.bcp vuoto e inserire i dati seguenti.

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:

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 esempio

SQL Server supporta due tipi di file di formato, ovvero non XML e XML. Il formato non XML è il formato originale supportato dalle versioni precedenti di SQL Server. Per altre informazioni, vedere Usare file in formato non XML (SQL Server).

Il comando seguente userà l' utility bcp per generare un formato di file non XML, myIdentity.fmt, sulla base dello schema di 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.
• L'opzione di formattazione richiede anche l'opzione -f.
• c viene usato per specificare i dati di tipo testo
• t, viene usato per specificare una virgola come carattere di terminazione del campo
• T viene usato per specificare una connessione attendibile tramite la sicurezza integrata.

Al prompt dei comandi immettere il comando seguente:

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. In caso contrario, è possibile che venga visualizzato il messaggio di errore seguente:

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

Esempi

Negli esempi vengono usati i file di database, file di dati e formato creati in questo articolo.

Usare bcp e mantenere i valori dell'identità senza un file di formato

L'interruttore -E.

Al prompt dei comandi immettere il comando seguente:

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

Usare bcp e mantenere i valori Identity con un file di formato non XML

Gli -E e -f interruttori.

Al prompt dei comandi immettere il comando seguente:

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

Usare bcp e i valori Identity generati senza un file di formato

Uso dei valori predefiniti.

Al prompt dei comandi immettere il comando seguente:

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

Usare bcp e i valori Identity generati con un file di formato non XML

Usare le impostazioni predefinite e -f interruttore.

Al prompt dei comandi immettere il comando seguente:

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

Usare BULK INSERT e mantenere i valori Identity senza un file di formato

Argomento KEEPIDENTITY.

Eseguire l'istruzione Transact-SQL seguente 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;

Usare BULK INSERT e mantenere i valori Identity con un file di formato non XML

Gli argomenti KEEPIDENTITY e FORMATFILE.

Eseguire l'istruzione Transact-SQL seguente 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;

Usare BULK INSERT e i valori Identity generati senza un file di formato

Uso dei valori predefiniti.

Eseguire l'istruzione Transact-SQL seguente 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;

Usare BULK INSERT e i valori Identity generati con un file di formato non XML

Uso di impostazioni predefinite e dell'argomento FORMATFILE.

Eseguire l'istruzione Transact-SQL seguente 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;

Usare OPENROWSET BULK e mantenere i valori Identity con un file di formato non XML

Il suggerimento della tabella KEEPIDENTITY e l'argomento FORMATFILE.

Eseguire l'istruzione Transact-SQL seguente 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;

Usare OPENROWSET BULK e i valori Identity generati con un file di formato non XML

Uso delle impostazioni predefinite e dell'argomento FORMATFILE.

Eseguire l'istruzione Transact-SQL seguente 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;

Attività correlate

• Mantenere i valori Null o i valori predefiniti durante un'importazione in blocco (SQL Server)

• Preparare i dati per l'importazione o l'esportazione in blocco

Per utilizzare un file di formato

• Creazione di un file di formato con bcp (SQL Server)

• Usare un file di formato per l'importazione in blocco dei dati (SQL Server)

• Usare un file di formato per eseguire il mapping tra le colonne di tabella e i campi del file di dati (SQL Server)

• Usare un file di formato per escludere un campo dati (SQL Server)

• Usare un file di formato per ignorare una colonna di una tabella (SQL Server)

Per utilizzare formati dati per l'importazione o l'esportazione in massa

• Importare dati in formato nativo e carattere da versioni precedenti di SQL Server

• Usare il formato carattere per importare o esportare dati (SQL Server)

• Usare il formato nativo per importare o esportare dati (SQL Server)

• Usare il formato carattere Unicode per importare o esportare dati (SQL Server)

• Uso del formato Unicode nativo per importare o esportare dati (SQL Server)

Per specificare i formati di dati per la compatibilità mediante bcp

1. Specificare i caratteri di terminazione dei campi e delle righe (SQL Server)

2. Specificare la lunghezza del prefisso nei file di dati usando bcp (SQL Server)

3. Specificare il tipo di archiviazione file usando bcp (SQL Server)

Contenuti correlati

• BACKUP (Transact-SQL)
• Utilità bcp
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• Hint di tabella (Transact-SQL)
• Formattare i file per importare ed esportare i dati (SQL Server)