Condividi tramite

BULK INSERT (Transact-SQL)

  • Articolo

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

Importa un file di dati in una tabella o vista di database in un formato specificato dall'utente in SQL Server

Convenzioni relative alla sintassi Transact-SQL

Sintassi

BULK INSERT
   { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] DATA_SOURCE = 'data_source_name' ]

   -- text formatting options
   [ [ , ] CODEPAGE = { 'RAW' | 'code_page' | 'ACP' | 'OEM' } ]
   [ [ , ] DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' } ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] FORMAT = 'CSV' ]
   [ [ , ] FIELDQUOTE = 'quote_characters']

   [ [ , ] FIRSTROW = first_row ]
   [ [ , ] LASTROW = last_row ]

   -- input file format options
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] FORMATFILE_DATA_SOURCE = 'data_source_name' ]

   -- error handling options
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ERRORFILE = 'file_name' ]
   [ [ , ] ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name' ]

   -- database options
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] TABLOCK ]

   -- source options
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch 
   [ [ , ] BATCHSIZE = batch_size ]

    )]

Argomenti

database_name

Nome del database contenente la tabella o la vista specificata. Se l'argomento database_name non è specificato, verrà usato il nome del database corrente.

schema_name

Consente di specificare il nome dello schema della tabella o della vista. schema_name è facoltativo se lo schema predefinito per l'utente che esegue l'operazione di importazione bulk corrisponde allo schema della tabella o della vista specificata. Se lo schema non è specificato e lo schema predefinito dell'utente che esegue l'operazione di importazione bulk è diverso dalla tabella o dalla vista specificata, SQL Server restituirà un messaggio di errore e l'operazione di importazione bulk verrà annullata.

table_name

Nome della tabella o della vista in cui eseguire l'importazione bulk dei dati. È possibile utilizzare solo le viste in cui tutte le colonne fanno riferimento alla stessa tabella di base. Per altre informazioni sulle restrizioni previste per il caricamento di dati all'interno di viste, vedere INSERT (Transact-SQL).

FROM 'data_file'

Specifica il percorso completo del file contenente i dati da importare nella tabella o nella vista specificata. L'istruzione BULK INSERT consente di importare dati da un disco o da Archiviazione BLOB di Azure (inclusi dischi floppy, unità di rete, dischi rigidi e così via).

In data_file deve essere specificato un percorso valido dal server in cui è in esecuzione SQL Server. Se data_file corrisponde a un file remoto, è necessario specificare il nome UNC (Universal Naming Convention). Il formato di un nome UNC è \\SystemName\ShareName\Path\FileName. Ad esempio:

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.dat';

A partire da SQL Server 2017 (14.x), il file specificato in data_file può essere presente in Archiviazione BLOB di Azure. In tal caso, è necessario specificare l'opzione data_source_name. Per un esempio, vedere Importare dati da un file in Archiviazione BLOB di Azure.

Database SQL di Azure supporta solo la lettura da Archiviazione BLOB di Azure.

BATCHSIZE = batch_size

Specifica il numero di righe in un batch. Ogni batch viene copiato nel server come singola transazione. In caso di errori, SQL Server eseguirà il commit o il rollback della transazione per ogni batch. Per impostazione predefinita, tutti i dati inclusi nel file di dati specificato costituiscono un batch. Per alcune considerazioni relative alle prestazioni, vedere la sezione Considerazioni sulle prestazioni disponibile più avanti in questo articolo.

CHECK_CONSTRAINTS

Specifica che tutti i vincoli sulla tabella o sulla vista di destinazione devono essere controllati durante l'operazione di importazione bulk. Se non si specifica l'opzione CHECK_CONSTRAINTS, i vincoli CHECK e FOREIGN KEY vengono ignorati e al termine dell'operazione il vincolo sulla tabella viene contrassegnato come non attendibile.

I vincoli UNIQUE e PRIMARY KEY vengono sempre applicati. Durante l'importazione in una colonna di tipo carattere definita con un vincolo NOT NULL, l'istruzione BULK INSERT determinerà l'inserimento di una stringa vuota qualora non sia presente alcun valore nel file di testo.

A un certo punto, sarà necessario esaminare i vincoli nell'intera tabella. Se prima dell'operazione di importazione bulk la tabella non era vuota, il costo per la riconvalida dei vincoli può essere superiore a quello correlato all'applicazione dei vincoli CHECK ai dati incrementali.

Una situazione in cui può essere necessario disabilitare i vincoli (comportamento predefinito) è rappresentata dal caso in cui i dati di input contengono righe che violano i vincoli. Se si disabilitano i vincoli CHECK, è possibile importare i dati e quindi utilizzare istruzioni Transact-SQL per rimuovere i dati non validi.

Nota

L'opzione MAXERRORS non viene applicata al controllo dei vincoli.

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

Specifica la tabella codici dei dati contenuti nel file di dati. CODEPAGE è pertinente solo se i dati contengono colonne di tipo char, varchar o text con valori carattere maggiori di 127 o minori di 32. Per un esempio, vedere Definire una tabella codici.

CODEPAGE non è un'opzione supportata in Linux per SQL Server 2017 (14.x). Per SQL Server 2019 (15.x), per CODEPAGE è consentita solo l'opzione 'RAW'.

Si consiglia di specificare un nome di regole di confronto per ogni colonna in un file di formato.

Valore CODEPAGE Descrizione
ACP Le colonne con tipo di dati char, varchar o text vengono convertite dalla tabella codici ANSI/Microsoft Windows (ISO 1252) nella tabella codici di SQL Server.
OEM (predefinito) Le colonne con tipo di dati char, varchar o text vengono convertire dalla tabella codici OEM di sistema nella tabella codici di SQL Server.
RAW Non vengono eseguite conversioni tra tabelle codici. RAW rappresenta l'opzione più rapida.
code_page Numero specifico della tabella codici, ad esempio 850.

Le versioni precedenti a SQL Server 2016 (13.x) non supportano la tabella codici 65001 (codifica UTF-8).

DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }

Specifica che l'operazione di importazione viene eseguita tramite BULK INSERT utilizzando il valore specificato per il tipo di file di dati.

Valore DATAFILETYPE Rappresentazione di tutti i dati
char (impostazione predefinita) Formato carattere.

Per altre informazioni, vedere Usare il formato carattere per importare o esportare dati (SQL Server).
native Tipi di dati nativi (database). Creare il file di dati nativo eseguendo l'importazione bulk dei dati da SQL Server tramite l'utilità bcp.

Il valore native rappresenta un'alternativa con prestazioni superiori rispetto al valore char. L'uso del formato nativo è consigliabile in caso di trasferimento bulk dei dati tra più istanze di SQL Server tramite un file di dati che non contiene caratteri estesi o DBCS (Double Byte Character Set).

Per altre informazioni, vedere Usare il formato nativo per importare o esportare dati (SQL Server).
widechar Caratteri Unicode.

Per altre informazioni, vedere Usare il formato carattere Unicode per importare o esportare dati (SQL Server).
widenative Tipi di dati nativi (database), ad eccezione delle colonne di tipo char, varchar e text, in cui i dati vengono archiviati in formato Unicode. Creare il file di dati widenative eseguendo l'importazione bulk dei dati da SQL Server tramite l'utilità bcp.

Il valore widenative rappresenta un'alternativa con prestazioni migliori rispetto a widechar. Se il file di dati contiene caratteri estesi ANSI, specificare il valore widenative.

Per altre informazioni, vedere Usare il formato Unicode nativo per importare o esportare dati (SQL Server).

DATA_SOURCE = 'data_source_name'

Si applica a: SQL Server 2017 (14.x), Database SQL di Azure.

Specifica un'origine dati esterna denominata che punta alla posizione Archiviazione BLOB di Azure del file che verrà importato. L'origine dati esterna deve essere creata tramite l'opzione TYPE = BLOB_STORAGE aggiunta in SQL Server 2017 (14.x). Per altre informazioni, vedere CREATE EXTERNAL DATA SOURCE. Per un esempio, vedere Importare dati da un file in Archiviazione BLOB di Azure.

ERRORFILE = 'error_file_path'

Specifica il file usato per raccogliere le righe che contengono errori di formattazione e non possono essere convertite in un set di righe OLE DB. Tali righe vengono copiate nel file degli errori dal file di dati così come sono.

Il file degli errori viene creato durante l'esecuzione del comando. Se il file esiste già, viene generato un errore. Viene inoltre creato un file di controllo con estensione .ERROR.txt, che fa riferimento a ogni riga inclusa nel file degli errori e fornisce la diagnostica degli errori. Una volta corretti gli errori, è possibile caricare i dati.

A partire da SQL Server 2017 (14.x), il file error_file_path può essere incluso in Archiviazione BLOB di Azure.

ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name'

Si applica a: SQL Server 2017 (14.x).

Specifica un'origine dati esterna denominata che punta alla posizione Archiviazione BLOB di Azure del file degli errori rilevati durante l'importazione. L'origine dati esterna deve essere creata tramite l'opzione TYPE = BLOB_STORAGE aggiunta in SQL Server 2017 (14.x). Per altre informazioni, vedere CREATE EXTERNAL DATA SOURCE.

FIRSTROW = first_row

Specifica il numero della prima riga da caricare. Il valore predefinito è la prima riga nel file di dati specificato. FIRSTROW è in base 1.

L'attributo FIRSTROW non consente di ignorare le intestazioni di colonna. L'istruzione BULK INSERT non prevede che le intestazioni possano essere ignorate. Se si sceglie di ignorare le righe, il motore di database SQL Server esaminerà solo i terminatori dei campi e non convaliderà i dati presenti nei campi delle righe ignorate.

FIRE_TRIGGERS

Specifica che gli eventuali trigger di inserimento definiti nella tabella di destinazione vengono eseguiti durante l'operazione di importazione bulk. Se sono presenti trigger definiti per operazioni INSERT nella tabella di destinazione, questi trigger vengono attivati per ogni batch completato.

Se non si specifica FIRE_TRIGGERS, non viene eseguito alcun trigger di inserimento.

FORMATFILE_DATA_SOURCE = 'data_source_name'

Si applica a: SQL Server 2017 (14.x).

Specifica un'origine dati esterna denominata che punta alla posizione Archiviazione BLOB di Azure del file di formato che definisce lo schema dei dati importati. L'origine dati esterna deve essere creata tramite l'opzione TYPE = BLOB_STORAGE aggiunta in SQL Server 2017 (14.x). Per altre informazioni, vedere CREATE EXTERNAL DATA SOURCE.

KEEPIDENTITY

Specifica che il valore o i valori Identity presenti nel file di dati importato devono essere usati per la colonna Identity. Se KEEPIDENTITY viene omesso, i valori Identity per la colonna vengono sottoposti a verifica, ma non vengono importati e SQL Server assegna automaticamente valori univoci in base ai valori di inizializzazione e di incremento specificati durante la creazione della tabella. Se il file di dati non contiene valori per la colonna Identity nella tabella o nella vista, usare un file di formato per specificare che durante l'importazione dei dati la colonna Identity nella tabella o nella vista deve essere ignorata. SQL Server assegnerà automaticamente valori univoci alla colonna. Per altre informazioni, vedere DBCC CHECKIDENT (Transact-SQL).

Per altre informazioni sul mantenimento dei valori Identity, vedere Mantenere i valori Identity durante l'importazione bulk dei dati (SQL Server).

KEEPNULLS

Specifica che durante l'operazione di importazione bulk le colonne vuote devono mantenere un valore Null. Ciò significa che durante l'operazione non verranno inseriti eventuali valori predefiniti per le colonne vuote. Per altre informazioni, vedere Mantenere i valori Null o usare i valori predefiniti durante l'importazione bulk (SQL Server).

KILOBYTES_PER_BATCH = kilobytes_per_batch

Specifica il numero approssimativo di kilobyte (KB) di dati per ogni batch come kilobytes_per_batch. Per impostazione predefinita, il valore KILOBYTES_PER_BATCH è sconosciuto. Per alcune considerazioni relative alle prestazioni, vedere la sezione Considerazioni sulle prestazioni disponibile più avanti in questo articolo.

LASTROW = last_row

Specifica il numero dell'ultima riga da caricare. Il valore predefinito è 0, che indica l'ultima riga nel file di dati specificato.

MAXERRORS = max_errors

Specifica il numero massimo di errori di sintassi consentiti nei dati prima dell'annullamento dell'operazione di importazione bulk. Qualsiasi riga che non sia possibile importare tramite l'operazione di importazione bulk viene ignorata e considerata come errore. Se il valore max_errors non è specificato, viene usato il valore predefinito, ovvero 10.

Non è possibile usare l'opzione MAX_ERRORS per le verifiche dei vincoli, né per la conversione di tipi di dati money e bigint.

ORDER ( { column [ ASC | DESC ] } [ ,... n ] )

Specifica il tipo di ordinamento dei dati nel file. Le prestazioni dell'importazione bulk sono migliori se i dati da importare vengono ordinati in base all'indice cluster della tabella, se disponibile. Se il file di dati viene ordinato in modo diverso rispetto all'ordine di una chiave di indice cluster o se la tabella non include un indice cluster, la clausola ORDER viene ignorata. I nomi di colonna specificati devono corrispondere a nomi di colonna validi nella tabella di destinazione. Per impostazione predefinita, per l'operazione di inserimento bulk si presume che il file di dati non sia ordinato. Per garantire l'ottimizzazione dell'importazione bulk, SQL Server verifica inoltre che i dati importati siano ordinati.

n è un segnaposto che indica la possibilità di specificare più colonne.

ROWS_PER_BATCH = rows_per_batch

Indica il numero approssimativo di righe di dati nel file di dati.

Per impostazione predefinita, tutti i dati nel file di dati vengono inviati al server come singola transazione e il numero di righe nel batch non è noto per Query Optimizer. Se si specifica ROWS_PER_BATCH (con un valore > 0), il server userà tale valore per ottimizzare l'operazione di importazione bulk. Il valore specificato per ROWS_PER_BATCH deve essere più o meno uguale al numero effettivo di righe. Per alcune considerazioni relative alle prestazioni, vedere la sezione Considerazioni sulle prestazioni disponibile più avanti in questo articolo.

TABLOCK

Imposta l'acquisizione di un blocco a livello di tabella per l'intera durata dell'operazione di importazione bulk. Una tabella può essere caricata simultaneamente da più client se non include indici e si specifica TABLOCK. Per impostazione predefinita, la modalità di blocco è determinata dall'opzione table lock on bulk loaddella tabella. Quando un blocco viene mantenuto attivo solo per la durata dell'operazione di importazione bulk, la contesa tra blocchi nella tabella viene ridotta, producendo in alcuni casi un miglioramento significativo delle prestazioni. Per alcune considerazioni relative alle prestazioni, vedere la sezione Considerazioni sulle prestazioni disponibile più avanti in questo articolo.

Il comportamento di blocco per un indice columnstore è diverso, perché tale indice è suddiviso internamente in più set di righe. Per ogni thread vengono caricati esclusivamente i dati di ciascun set di righe tramite l'applicazione di un blocco X al set di righe, consentendo il caricamento parallelo dei dati con sessioni di caricamento dati simultanee. L'uso dell'opzione TABLOCK causa l'applicazione da parte del thread di un blocco X sulla tabella (a differenza del blocco aggiornamenti bulk per i set di righe tradizionali), che impedisce ad altri thread simultanei di caricare dati contemporaneamente.

Opzioni di formato del file di input

FORMAT = 'CSV'

Si applica a: SQL Server 2017 (14.x).

specifica un file di valori delimitati da virgole conforme allo standard RFC 4180.

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.csv'
WITH ( FORMAT = 'CSV');

FIELDQUOTE = 'field_quote'

Si applica a: SQL Server 2017 (14.x).

Specifica il carattere da usare come carattere virgolette nel file CSV. Se non viene specificato alcun carattere, viene usato il carattere virgolette (") in base alla definizione dello standard RFC 4180.

FORMATFILE = 'format_file_path'

Specifica il percorso completo di un file di formato. Un file di formato descrive il file di dati contenente le risposte archiviate create tramite l'utilità bcp nella stessa tabella o vista. È consigliabile utilizzare il file di formato nei casi seguenti:

  • Il file di dati contiene un numero di colonne maggiore o minore rispetto alla tabella o alla vista.
  • Le colonne sono in un ordine diverso.
  • I delimitatori di colonna variano.
  • Sono presenti altre modifiche di formato dei dati. I file di formato vengono in genere creati tramite l'utilità bcp e modificati in un editor di testo in base alle esigenze. Per altre informazioni, vedere Utilità bcp e Creare un file di formato.

In SQL Server 2017 (14.x) e versioni successive e in Database SQL di Azure format_file_path può essere incluso in Archiviazione BLOB di Azure.

FIELDTERMINATOR = 'field_terminator'

Specifica il carattere di terminazione del campo da usare per i file di dati di tipo char e widechar. Il carattere di terminazione del campo predefinito è \t (carattere di tabulazione). Per altre informazioni, vedere Impostazione dei caratteri di terminazione del campo e della riga (SQL Server).

ROWTERMINATOR = 'row_terminator'

Specifica il carattere di terminazione della riga da usare per i file di dati di tipo char e widechar. Il carattere di terminazione della riga predefinito è \r\n (carattere di nuova riga). Per altre informazioni, vedere Impostazione dei caratteri di terminazione del campo e della riga (SQL Server).

Compatibilità

L'istruzione BULK INSERT consente di applicare meccanismi restrittivi di convalida e controllo dei dati che vengono letti da un file e che potrebbero causare errori negli script esistenti nel caso in cui tali script vengano eseguiti con dati non validi. L'istruzione BULK INSERT, ad esempio, prevede le verifiche seguenti:

  • Validità delle rappresentazioni native dei tipi di dati float o real.
  • Lunghezza in byte pari dei dati Unicode.

Tipo di dati

Conversioni del tipo di dati da string a decimal

Le conversioni del tipo di dati da string a decimal usate in BULK INSERT seguono le stesse regole della funzione CONVERT Transact-SQL, che rifiuta le stringhe che rappresentano valori numerici con notazione scientifica. Tali stringhe vengono quindi considerate da BULK INSERT come valori non validi e vengono segnalati errori di conversione.

Per risolvere il problema, usare un file di formato per eseguire l'importazione bulk dei dati float in notazione scientifica in una colonna decimale. Nel file di formato, descrivere in modo esplicito la colonna come dati real o float. Per altre informazioni su questi tipi di dati, vedere Tipi di dati float e real (Transact-SQL).

I file di formato rappresentano i dati real con il tipo di dati SQLFLT4 e i dati float con il tipo di dati SQLFLT8. Per informazioni su file di formato non XML, vedere Specifica del tipo di archiviazione di file tramite bcp (SQL Server).

Esempio di importazione di un valore numerico con notazione scientifica

In questo esempio viene utilizzata la seguente tabella del database bulktest:

CREATE TABLE dbo.t_float(c1 FLOAT, c2 DECIMAL (5,4));

Si desidera eseguire l'importazione bulk di dati nella tabella t_float. Il file di dati, C:\t_float-c.dat, contiene dati float in notazione scientifica, ad esempio:

8.0000000000000002E-2 8.0000000000000002E-2

Quando si copia questo esempio, tenere presente che diversi editor di testo e codifiche salvano i caratteri di tabulazione (\t) come spazi. Un carattere di tabulazione viene presentato più avanti in questo esempio.

Tramite l'istruzione BULK INSERT, tuttavia, non è possibile importare tali dati direttamente in t_float, in quanto la seconda colonna, c2, usa il tipo di dati decimal. È pertanto necessario un file di formato. Il file di formato deve eseguire il mapping dei dati float in notazione scientifica al formato decimale della colonna c2.

Nel file di formato seguente viene utilizzato il tipo di dati SQLFLT8 per eseguire il mapping del secondo campo dati alla seconda colonna:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/> </RECORD> <ROW>
<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLFLT8"/> </ROW> </BCPFORMAT>

Per importare i dati di test nella tabella di test tramite questo file di formato (usando il nome di file C:\t_floatformat-c-xml.xml), eseguire la seguente istruzione Transact-SQL:

BULK INSERT bulktest.dbo.t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE = 'C:\t_floatformat-c-xml.xml');

Importante

Database SQL di Azure supporta solo la lettura da Archiviazione BLOB di Azure.

Tipi di dati per l'esportazione o l'importazione bulk di documenti SQLXML

Per eseguire l'esportazione o l'importazione bulk di dati SQLXML, utilizzare uno dei tipi di dati seguenti nel file di formato:

Tipo di dati Effetto
SQLCHAR o SQLVARCHAR I dati vengono inviati nella tabella codici del client o nella tabella codici implicita delle regole di confronto. I risultati sono uguali a quelli ottenuti specificando DATAFILETYPE = 'char' senza indicare un file di formato.
SQLNCHAR o SQLNVARCHAR I dati vengono inviati in formato Unicode. L'effetto è lo stesso ottenuto specificando DATAFILETYPE = 'widechar' senza indicare un file di formato.
SQLBINARY o SQLVARBIN I dati vengono inviati senza conversione.

Osservazioni:

Per un confronto tra l'istruzione BULK INSERT, l'istruzione INSERT ... SELECT * FROM OPENROWSET(BULK...) e il comando bcp, vedere Informazioni sull'importazione ed esportazione bulk di dati (SQL Server).

Per informazioni sulla preparazione dei dati per operazioni di importazione bulk, vedere Preparazione dei dati per l'importazione o l'esportazione bulk (SQL Server).

L'istruzione BULK INSERT può essere eseguita all'interno di una transazione definita dall'utente per importare dati in una tabella o una vista. Facoltativamente, per utilizzare più corrispondenze per l'importazione bulk dei dati, una transazione può consentire di specificare la clausola BATCHSIZE nell'istruzione BULK INSERT. Se una transazione con più batch viene sottoposta a rollback, tale operazione viene effettuata per ogni singolo batch inviato a SQL Server dalla transazione.

Interoperabilità

Importare dati da un file CSV

A partire da SQL Server 2017 (14.x), l'istruzione BULK INSERT supporta il formato CSV, analogamente a Database SQL di Azure.

Nelle versione precedenti al rilascio di SQL Server 2017 (14.x), i file con valori delimitati da virgole (CSV) non sono supportati per le operazioni di importazione bulk in SQL Server. In alcuni casi, tuttavia, è possibile utilizzare un file CSV come file di dati per un'importazione bulk di dati in SQL Server. Per informazioni sui requisiti per l'importazione di dati da un file di dati CSV, vedere Preparazione dei dati per l'importazione o l'esportazione bulk (SQL Server).

Comportamento del log

Per informazioni sui casi in cui le operazioni di inserimento di righe eseguite durante l'importazione in blocco in SQL Server vengono registrate nel log delle transazioni, vedere Prerequisiti per la registrazione minima nell'importazione in blocco. La registrazione minima non è supportata nel database SQL di Azure.

Restrizioni

Quando si utilizza un file di formato con BULK INSERT, è possibile specificare al massimo 1024 campi. Si tratta dello stesso numero massimo di colonne consentite in una tabella. Se si usa un file di formato con BULK INSERT insieme a un file di dati contenente più di 1024 campi, verrà generato l'errore 4822. L'utilità bcp non presenta questa limitazione. Pertanto, per i file di dati contenenti più di 1024 campi, è possibile usare l'istruzione BULK INSERT senza alcun file di formato oppure il comando bcp.

Considerazioni sulle prestazioni

Se il numero di pagine da scaricare in un singolo batch supera una soglia interna, è possibile che venga eseguita un'analisi completa del pool di buffer per identificare le pagine da scaricare durante il commit del batch. L'analisi completa può influire sulle prestazioni dell'importazione bulk. Un probabile caso di superamento della soglia interna si verifica quando un pool di buffer di grandi dimensioni è combinato a un sottosistema di I/O lento. Per evitare gli overflow del buffer in sistemi di grandi dimensioni, non usare l'hint TABLOCK (che elimina l'ottimizzazione delle operazioni bulk) o usare un batch di dimensioni minori (che mantiene l'ottimizzazione delle operazioni bulk).

È consigliabile testare diverse dimensioni del batch con il carico dati effettivo in modo da individuare la soluzione più adatta. Tenere presente che le dimensioni del batch hanno implicazioni di rollback parziale. Se il processo ha esito negativo, prima di usare di nuovo BULK INSERT, potrebbe essere necessario eseguire operazioni manuali aggiuntive per rimuovere una parte delle righe inserite correttamente prima di un errore.

Se si usa il database SQL di Azure e si esegue l'importazione di una quantità elevata di dati, valutare l'opportunità di aumentare temporaneamente il livello delle prestazioni del database o dell'istanza prima dell'importazione.

Sicurezza

Delega degli account di sicurezza (rappresentazione)

Se un utente usa un account di accesso SQL Server, viene usato il profilo di sicurezza dell'account del processo di SQL Server. Un accesso eseguito tramite l'autenticazione di SQL Server non può essere autenticato all'esterno del motore di database. Pertanto, quando un comando BULK INSERT viene avviato da un account di accesso che utilizza l'autenticazione di SQL, la connessione ai dati viene effettuata utilizzando il contesto di sicurezza dell'account del processo di SQL Server (l'account utilizzato dal servizio Motore di database di SQL Server).

Per una lettura corretta dei dati di origine è necessario concedere all'account utilizzato dal motore di database di SQL Server l'accesso ai dati di origine. Di contro, se un utente di SQL Server esegue l'accesso utilizzando l'autenticazione di Windows, potrà leggere solo i file accessibili dall'account utente, indipendentemente dal profilo di sicurezza del processo di SQL Server.

Quando si esegue l'istruzione BULK INSERT usando sqlcmd o osql da un primo computer, si inseriscono i dati in SQL Server in un secondo computer e si specifica un parametro data_file in un terzo computer tramite un percorso UNC, è possibile che venga restituito l'errore 4861.

Per risolvere questo errore, usare l'autenticazione di SQL Server e specificare un account di accesso di SQL Server che usi il profilo di sicurezza dell'account del processo di SQL Server. In alternativa, configurare Windows in modo da consentire la delega degli account di sicurezza. Per informazioni sull'abilitazione di un account utente in modo che venga considerato attendibile per la delega, vedere la Guida di Windows.

Per altre considerazioni sulla sicurezza relativa all'uso dell'istruzione BULK INSERT, vedere Importare dati per operazioni bulk usando BULK INSERT o OPENROWSET(BULK...) (SQL Server).

Quando si esegue l'importazione da Archiviazione BLOB di Azure e i dati non sono pubblici (accesso anonimo), creare credenziali con ambito database basate su una chiave di firma di accesso condiviso crittografata con una chiave master e, quindi, creare un'origine del database esterna da usare nel comando BULK INSERT.

In alternativa, creare un DATABASE SCOPED CREDENTIAL basato su MANAGED IDENTITY per autorizzare le richieste di accesso ai dati in account di archiviazione non pubblici. Quando si usa MANAGED IDENTITY, Archiviazione di Azure deve concedere le autorizzazioni all'identità gestita dell'istanza aggiungendo il ruolo predefinito di Azure di controllo degli accessi in base al ruolo Collaboratore ai dati del BLOB di archiviazione che fornisce l'accesso in lettura/scrittura all'identità gestita per i contenitori Archiviazione BLOB di Azure necessari. Istanza gestita di SQL di Azure dispone di un'identità gestita assegnata dal sistema e può avere anche una o più identità gestite assegnate dall'utente. È possibile usare identità gestite assegnate dal sistema o identità gestite assegnate dall'utente per autorizzare le richieste. Per l'autorizzazione, verrà usata l'identità default dell'istanza gestita , ovvero l'identità gestita assegnata dall'utente primaria o l'identità gestita assegnata dal sistema se non è specificata l'identità gestita assegnata dall'utente. Per un esempio, vedere Importare dati da un file in Archiviazione BLOB di Azure.

Importante

L'identità gestita è applicabile solo a SQL di Azure. SQL Server non supporta l’identità gestita.

Autorizzazioni

Sono necessarie le autorizzazioni INSERT e ADMINISTER BULK OPERATIONS. Nel database SQL di Azure, sono necessarie le autorizzazioni INSERT e ADMINISTER DATABASE BULK OPERATIONS. Per SQL Server in Linux, non sono supportate né le autorizzazioni ADMINISTER BULK OPERATIONS, né il ruolo bulkadmin. Per SQL Server in Linux, gli inserimenti bulk possono essere eseguiti solo dal ruolo sysadmin.

È inoltre richiesta l'autorizzazione ALTER TABLE se si verifica anche una sola condizione tra le seguenti:

  • Sono presenti vincoli e l'opzione CHECK_CONSTRAINTS non è specificata.

    Per impostazione predefinita, i vincoli sono disabilitati. Per verificare i vincoli in modo esplicito, utilizzare l'opzione CHECK_CONSTRAINTS.

  • Sono presenti trigger e l'opzione FIRE_TRIGGER non è specificata.

    Per impostazione predefinita, i trigger non sono attivati. Per attivare i trigger in modo esplicito, utilizzare l'opzione FIRE_TRIGGERS.

  • Si utilizza l'opzione KEEPIDENTITY per importare valori Identity dal file di dati.

Esempi

R. Usare le pipe per importare dati da un file

Nell'esempio seguente vengono importate informazioni dettagliate sugli ordini nella tabella AdventureWorks2022.Sales.SalesOrderDetail dal file di dati specificato, utilizzando una barra verticale (|) come carattere di terminazione del campo e la combinazione |\n come carattere di terminazione della riga.

BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
      (
         FIELDTERMINATOR = ' |'
         , ROWTERMINATOR = ' |\n'
      );

Importante

Database SQL di Azure supporta solo la lettura da Archiviazione BLOB di Azure.

B. Usare l'argomento FIRE_TRIGGERS

Nell'esempio seguente viene specificato l'argomento FIRE_TRIGGERS.

BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
         FIELDTERMINATOR = ' |'
         , ROWTERMINATOR = ':\n'
         , FIRE_TRIGGERS
      );

Importante

Database SQL di Azure supporta solo la lettura da Archiviazione BLOB di Azure.

C. Usare il carattere di avanzamento riga come carattere di terminazione della riga

Nell'esempio seguente viene importato un file che utilizza il carattere di avanzamento riga come carattere di terminazione della riga, come nel caso di output UNIX:

DECLARE @bulk_cmd VARCHAR(1000);
SET @bulk_cmd = 'BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>''
WITH (ROWTERMINATOR = '''+CHAR(10)+''')';
EXEC(@bulk_cmd);

Nota

A causa del modo in cui Microsoft Windows tratta i file di testo, \n viene sostituito automaticamente con \r\n.

Importante

Database SQL di Azure supporta solo la lettura da Archiviazione BLOB di Azure.

D. Definire una tabella codici

Nell'esempio che segue viene illustrato come specificare una tabella codici.

BULK INSERT MyTable
FROM 'D:\data.csv'
WITH
( CODEPAGE = '65001'
   , DATAFILETYPE = 'char'
   , FIELDTERMINATOR = ','
);

Importante

Database SQL di Azure supporta solo la lettura da Archiviazione BLOB di Azure.

E. Importare dati da un file CSV

Nell'esempio che segue viene illustrato come specificare un file CSV ignorando l'intestazione (prima riga), usando ; come carattere di terminazione del campo e 0x0a come carattere di terminazione della riga:

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH (FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

Nell'esempio che segue viene illustrato come specificare un file CSV nel formato UTF-8 (tramite un CODEPAGE di 65001) ignorando l'intestazione (prima riga), usando ; come carattere di terminazione del campo e 0x0a come carattere di terminazione della riga:

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH ( CODEPAGE = '65001'
      , FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

Importante

Database SQL di Azure supporta solo la lettura da Archiviazione BLOB di Azure.

F. Importare dati da un file in Archiviazione BLOB di Azure

L'esempio seguente illustra come caricare dati da un file CSV in un percorso di Archiviazione BLOB di Azure in cui è stata creata una firma di accesso condiviso . La posizione Archiviazione BLOB di Azure è configurata come origine dati esterna, per la quale sono richieste credenziali con ambito database contenenti una chiave di firma di accesso condiviso crittografata tramite una chiave master nel database utente.

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

-- NOTE: Make sure that you don't have a leading ? in SAS token, and
-- that you have at least read permission on the object that should be loaded srt=o&sp=r, and
-- that expiration period is valid (all dates are in UTC time)

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL = MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

L'esempio seguente illustra come usare il comando BULK INSERT per caricare dati da un file CSV in una posizione di Archiviazione BLOB di Azure utilizzando l’identità gestita. La posizione di Archiviazione BLOB di Azure è configurata come origine dati esterna.

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential 
WITH IDENTITY = 'Managed Identity';
-- NOTE: Make sure you have granted Storage Bob Data Contributor RBAC on storage to provides read/write access to the managed identity for the necessary Azure Blob Storage containers.
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);
BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

Importante

L'identità gestita è applicabile solo a SQL di Azure. SQL Server non supporta l’identità gestita.

Importante

SQL di Azure supporta solo la lettura da Archiviazione BLOB di Azure.

G. Importare dati da un file in Archiviazione BLOB di Azure e specificare un file degli errori

Nell'esempio che segue viene illustrato come caricare dati da un file csv in una posizione Archiviazione BLOB di Azure configurata come origine dati esterna e come specificare un file degli errori. Per queste operazioni, sono necessarie credenziali con ambito database contenenti una firma di accesso condiviso. Se l'esecuzione in Database SQL di Azure, l'opzione ERRORFILE deve essere accompagnata da ERRORFILE_DATA_SOURCE. Altrimenti, è probabile che l'importazione abbia esito negativo e venga restituito un errore di autorizzazione. Il file specificato in ERRORFILE non deve essere presente nel contenitore.

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (
         DATA_SOURCE = 'MyAzureInvoices'
         , FORMAT = 'CSV'
         , ERRORFILE = 'MyErrorFile'
         , ERRORFILE_DATA_SOURCE = 'MyAzureInvoices');

Per esempi di BULK INSERT completi che includono la configurazione di credenziali e di un'origine dati esterna, vedere Esempi di accesso bulk ai dati nell'archiviazione BLOB di Azure.

Altri esempi

Altri esempi relativi a BULK INSERT sono disponibili negli articoli seguenti:

Vedi anche