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

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Analytics 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).

Struttura

Mantenere i valori Identity Condizioni di test di esempio  ● Tabella di esempio  ● File di dati di esempio  ● File di formato non XML di esempio Esempi  ● Uso di bcp e mantenimento dei valori Identity senza un file di formato  ● Uso di bcp e mantenimento dei valori Identity con un file di formato non XML  ● Uso di bcp e dei valori Identity generati senza un file di formato  ● Uso di bcp e dei valori Identity generati con un file di formato non XML  ● Uso di BULK INSERT e mantenimento dei valori Identity senza un file di formato  ● Uso di BULK INSERT e mantenimento dei valori Identity con un file di formato non XML  ● Uso di BULK INSERT e dei valori Identity generati senza un file di formato  ● Uso di BULK INSERT e dei valori Identity generati con un file di formato non XML  ● Uso di OPENROWSET e mantenimento dei valori Identity con un file di formato non XML  ● Uso di OPENROWSET e dei valori Identity generati con un file di formato non XML

Mantenere i valori Identity

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. Sono disponibili i qualificatori seguenti:

Comando	Qualificatore per il mantenimento dei valori Identity	Tipo di qualificatore
bcp	-E	Switch
BULK INSERT	KEEPIDENTITY	Argomento
INSERT ... SELECT * FROM OPENROWSET(BULK...).	KEEPIDENTITY	Hint di tabella

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

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 informazioni dettagliate, vedere 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 format richiede anche l'opzione -f . 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. 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

Gli esempi seguenti usano il database, il file di dati e i file di formato creati in precedenza.

Uso di bcp e mantenimento dei valori Identity senza un file di formato

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

Uso di bcp e mantenimento dei valori Identity con un file di formato non XML

Opzioni-E e -f . 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;"

Uso di bcp e dei 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;"

Uso di bcp e dei valori Identity generati con un file di formato non XML

Uso dei valori predefiniti e dell'opzione -f . 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;"

Uso di BULK INSERT e mantenimento dei valori Identity senza un file di formato

ArgomentoKEEPIDENTITY . 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;

Uso di BULK INSERT e mantenimento dei valori Identity con un file di formato non XML

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

Uso di BULK INSERT e dei 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;

Uso di BULK INSERT e dei valori Identity generati con un file di formato non XML

Uso dei valori predefiniti 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;

Uso di OPENROWSET(BULK...) e mantenimento dei valori Identity con un file di formato non XML

Hint di tabellaKEEPIDENTITY e 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;

Uso di OPENROWSET(BULK...) e dei valori Identity generati con un file di formato non XML

Uso dei valori predefiniti 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

• Mantenimento dei valori Null o utilizzo dei valori predefiniti durante un'importazione bulk (SQL Server)

• Preparazione dei dati per l'importazione o l'esportazione bulk (SQL Server)

Per utilizzare un file di formato

• Creazione di un file di formato (SQL Server)

• Usare un file di formato per l'importazione bulk 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 di dati per l'importazione o l'esportazione bulk

• 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. Impostazione dei caratteri di terminazione del campo e della riga (SQL Server)

2. Specifica della lunghezza del prefisso nei file di dati tramite bcp (SQL Server)

3. Specifica del tipo di archiviazione di file tramite bcp (SQL Server)

Vedi anche

BACKUP (Transact-SQL)
Utilità bcp
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Hint di tabella (Transact-SQL)
File di formato per l'importazione o l'esportazione di dati (SQL Server)