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

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di sistemi analitici (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

Per utilizzare un file di formato

Per utilizzare formati di dati per l'importazione o l'esportazione bulk

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)