Condividi tramite


BULK INSERT (Transact-SQL)

Data aggiornamento: 12 dicembre 2006

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

Icona di collegamento a un argomentoConvenzioni della sintassi Transact-SQL

Sintassi

BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
        ( 
   [ [ , ] BATCHSIZE = batch_size ] 
   [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] KEEPIDENTITY ] 
   [ [ , ] KEEPNULLS ] 
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
   [ [ , ] LASTROW = last_row ] 
   [ [ , ] MAXERRORS = max_errors ] 
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] 
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
   [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
   [ [ , ] TABLOCK ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
        )] 

Argomenti

  • database_name
    Nome del database contenente la tabella o vista specificata. Se viene omesso, il valore predefinito è il database corrente.
  • schema_name
    Nome dello schema della tabella o della vista. schema_name è facoltativo se lo schema predefinito per l'utente che esegue l'operazione di importazione di massa corrisponde allo schema della tabella o della vista specificata. Se schema non viene specificato e lo schema predefinito dell'utente che esegue l'operazione di importazione di massa è diverso rispetto alla tabella o alla vista specificata, SQL Server restituisce un messaggio di errore e l'operazione di importazione di massa viene annullata.
  • table_name
    Nome della tabella o vista in cui eseguire l'importazione di massa dei dati. È possibile utilizzare solo le viste in cui tutte le colonne fanno riferimento alla stessa tabella di base. Per ulteriori informazioni sulle restrizioni relative al caricamento di dati nelle viste, vedere INSERT (Transact-SQL).
  • 'data_file'
    Percorso completo del file contenente i dati da importare nella tabella o vista specificata. L'istruzione BULK INSERT consente di importare dati da un disco, ad esempio unità di rete, dischi floppy, dischi rigidi e così via.

    L'argomento data_file deve specificare un percorso valido dal server in cui è in esecuzione SQL Server. Se data_file corrisponde a un file remoto, è necessario specificare il nome in formato UNC (Universal Naming Convention).

  • 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 esegue 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 ulteriori informazioni, vedere Gestione dei batch per l'importazione di massa.

  • CHECK_CONSTRAINTS
    Specifica che tutti i vincoli sulla tabella o vista di destinazione devono essere controllati durante l'operazione di importazione di massa. 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 trusted.

    [!NOTA] I vincoli UNIQUE, PRIMARY KEY e NOT NULL vengono sempre applicati.

    A un certo punto è comunque necessario esaminare i vincoli nell'intera tabella. Se la tabella non è vuota prima dell'operazione di importazione di massa, l'impegno per la riconvalida dei vincoli può essere superiore all'impegno correlato all'applicazione dei vincoli CHECK ai dati incrementali.

    Una situazione in cui potrebbe essere utile disattivare i vincoli (impostazione predefinita) è quando i dati di input contengono righe che violano i vincoli. Disattivando 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.

    [!NOTA] In SQL Server 2005, l'istruzione BULK INSERT applica nuovi meccanismi di convalida e controllo dei dati che potrebbero causare errori per gli script esistenti eseguiti su dati non validi in un file di dati.

    Per ulteriori informazioni, vedere Controllo della verifica dei vincoli tramite operazioni di importazione di massa.

  • CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }
    Specifica la tabella codici dei dati contenuti nel file. CODEPAGE è rilevante solo se i dati contengono colonne di tipo char, varchar o text con valori di carattere maggiori di 127 o minori di 32.

    Valore CODEPAGE Descrizione

    ACP

    Le colonne con tipo di dati char, varchar o text vengono convertite dalla tabella codici ANSI/Microsoft Windows (ISO 1252) alla tabella codici SQL Server.

    OEM (valore predefinito)

    Le colonne con tipo di dati char, varchar o text vengono convertite dalla tabella codici OEM del sistema alla tabella codici SQL Server.

    RAW

    Non viene eseguita alcuna conversione da una tabella codici a un'altra. Questa è l'opzione più rapida.

    code_page

    Numero di tabella codici specifico, ad esempio 850.

    Per ulteriori informazioni, vedere Copia di dati tra regole di confronto diverse.

  • 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 (valore predefinito)

    Formato carattere.

    Per ulteriori informazioni, vedere Importazione ed esportazione di dati in base al formato carattere.

    native

    Tipi di dati nativi (database). Creare il file di dati nativo tramite l'importazione di massa dei dati da SQL Server con l'utilità bcp.

    Il valore native rappresenta un'alternativa con prestazioni superiori rispetto al valore char.

    Per ulteriori informazioni, vedere Utilizzo del formato nativo per l'importazione o l'esportazione di dati.

    widechar

    Caratteri Unicode.

    Per ulteriori informazioni, vedere Utilizzo del formato carattere Unicode per l'importazione o l'esportazione di dati.

    widenative

    Tipi di dati nativi (database), con l'eccezione delle colonne di tipo char, varchar e text in cui i dati vengono archiviati in formato Unicode. Creare il file di dati widenative tramite l'importazione di massa dei dati da SQL Server con 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 widenative.

    Per ulteriori informazioni, vedere Utilizzo del formato Unicode nativo per importare o esportare dati.

  • FIELDTERMINATOR ='field_terminator'
    Specifica il carattere di terminazione del campo da utilizzare per i file di dati di tipo char e widechar. Il carattere di terminazione del campo predefinito è il carattere di tabulazione (\t). Per ulteriori informazioni, vedere Impostazione dei caratteri di terminazione del campo e della riga.
  • FIRSTROW **=**first_row
    Specifica il numero della prima riga da caricare. Il valore predefinito è la prima riga nel file di dati specificato.

    [!NOTA] L'attributo FIRSTROW non è progettato per ignorare le intestazioni di colonna. Quando vengono ignorate righe specifiche, Motore di database di SQL Server esegue la ricerca solo nei caratteri di terminazione dei campi e non convalida i dati 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 di massa. Se nella tabella di destinazione sono definiti trigger per le operazioni INSERT, questi vengono attivati per ogni batch completato.

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

    Per ulteriori informazioni, vedere Controllo dell'esecuzione dei trigger durante l'importazione di massa di dati.

  • 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 mediante l'utilità bcp sulla stessa tabella o vista. È consigliabile utilizzare il file di formato nei casi seguenti:

    • Il file di dati contiene un numero di colonne superiore o inferiore rispetto alla tabella o 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 generalmente creati con l'utilità bcp e modificati in un editor di testo in base alle esigenze. Per ulteriori informazioni, vedere Utilità bcp.
  • KEEPIDENTITY
    Specifica che i valori Identity presenti nel file di dati importato devono essere utilizzati per la colonna Identity. Se KEEPIDENTITY viene omesso, i valori Identity per questa colonna vengono verificati ma non importati e SQL Server assegna automaticamente valori univoci in base ai valori di inizializzazione e di incremento specificati in fase di creazione della tabella. Se il file di dati non contiene valori per la colonna Identity della tabella o vista, utilizzare un file di formato per specificare che durante l'importazione di dati la colonna Identity della tabella o vista deve essere ignorata. SQL Server assegna automaticamente valori univoci alla colonna. Per ulteriori informazioni, vedere DBCC CHECKIDENT (Transact-SQL).

    Per ulteriori informazioni sul mantenimento dei valori Identity, vedere Mantenimento dei valori Identity durante l'importazione di massa dei dati.

  • KILOBYTES_PER_BATCH = kilobytes_per_batch
    Specifica il numero approssimativo di kilobyte (KB) di dati per ogni batch. Per impostazione predefinita, il valore KILOBYTES_PER_BATCH è sconosciuto.

    Per ulteriori informazioni, vedere Gestione dei batch per l'importazione di massa.

  • 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 di massa. Ogni riga che non è possibile importare tramite l'operazione di importazione di massa viene ignorata e considerata come errore. Se max_errors viene omesso, il valore predefinito è 10.

    [!NOTA] L'opzione MAX_ERRORS non è applicabile alle verifiche dei vincoli o alla 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 di massa 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 indici cluster, la clausola ORDER viene ignorata. I nomi di colonna specificati devono corrispondere a colonne valide nella tabella di destinazione. Per impostazione predefinita, per l'operazione di inserimento di massa si presume che il file di dati non sia ordinato. Per garantire l'ottimizzazione dell'importazione di massa, SQL Server verifica inoltre che i dati importati siano ordinati.

    Per ulteriori informazioni, vedere Controllo dell'ordinamento durante l'importazione di massa di dati.

  • n
    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 è sconosciuto per Query Optimizer. Se si specifica ROWS_PER_BATCH (con valore > 0), il server utilizza questo valore per ottimizzare l'operazione di importazione di massa. Il valore specificato per ROWS_PER_BATCH deve essere più o meno uguale al numero effettivo di righe.

    Per ulteriori informazioni, vedere Gestione dei batch per l'importazione di massa.

  • ROWTERMINATOR ='row_terminator'
    Specifica il carattere di terminazione della riga da utilizzare per i file di dati di tipo char e widechar. Il carattere di terminazione della riga predefinito è \r\n (carattere di nuova riga). Per ulteriori informazioni, vedere Impostazione dei caratteri di terminazione del campo e della riga.
  • TABLOCK
    Imposta l'acquisizione di un blocco a livello di tabella per l'intera durata dell'operazione di importazione di massa. 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 load della tabella. Quando un blocco viene mantenuto attivo solo per la durata dell'operazione di importazione di massa, la contesa tra blocchi sulla tabella risulta ridotta, producendo un miglioramento significativo delle prestazioni.

    Per ulteriori informazioni, vedere Controllo della modalità di blocco per l'importazione di massa.

  • ERRORFILE ='file_name'
    Specifica il file in cui 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. Questo file contiene un riferimento a ogni riga nel file degli errori e offre informazioni di diagnostica. Dopo la correzione degli errori, i dati possono essere caricati.

Osservazioni

Per informazioni sulla preparazione dei dati per l'importazione di massa, vedere Preparazione dei dati per l'importazione o l'esportazione di massa.

È possibile eseguire l'istruzione BULK INSERT in una transazione definita dall'utente. Quando viene eseguito il rollback di una transazione definita dall'utente che utilizza l'istruzione BULK INSERT e la clausola BATCHSIZE per l'importazione dei dati in una tabella o vista tramite più batch, viene eseguito il rollback di tutti i batch inviati a SQL Server.

Per informazioni su quando le operazioni di inserimento di righe eseguite durante l'importazione di massa vengono registrate nel log delle transazioni, vedere Prerequisiti per la registrazione minima nell'importazione di massa.

In SQL Server 2005, l'istruzione BULK INSERT applica nuovi meccanismi più restrittivi di convalida e controllo dei dati letti da un file che potrebbero causare errori per gli script esistenti eseguiti su dati non validi. Ad esempio, l'istruzione BULK INSERT prevede ora le verifiche seguenti:

  • Validità delle rappresentazioni native dei tipi di dati float o real.
  • Controllo che i dati Unicode abbiano una lunghezza in byte pari.

Alcune forme di dati non validi che possono essere incluse senza problemi in un'importazione di massa in versioni precedenti di SQL Server, potrebbero non essere caricati nella versione corrente. Nelle versioni precedenti di SQL Server, non si verifica alcun errore finché un client non tenta di accedere a dati non validi. La convalida più rigorosa applicata in SQL Server 2005 riduce il rischio di errori durante l'esecuzione di query sui dati in seguito all'importazione di massa.

Esportazione o importazione di massa di documenti SQLXML

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

Tipo di dati Effetto

SQLCHAR o SQLVARYCHAR

I dati vengono inviati nella tabella codici del client o nella tabella codici implicita delle regole di confronto. L'effetto è lo stesso ottenuto specificando DATAFILETYPE ='char' senza indicare un file di formato.

SQLNCHAR o SQLNVARCHAR

I dati vengono inviati come Unicode. L'effetto è lo stesso ottenuto specificando DATAFILETYPE = 'widechar' senza indicare un file di formato.

SQLBINARY o SQLVARYBIN

I dati vengono inviati senza conversione.

Conversione del tipo da string a decimal

In SQL Server 2005, le conversioni del tipo da string a decimal utilizzate in BULK INSERT seguono le stesse regole della funzione CONVERT di Transact-SQL, che rifiuta le stringhe che rappresentano valori numerici con notazione scientifica. Pertanto, BULK INSERT considera tali stringhe valori non validi e genera un errore di conversione.

[!NOTA] In SQL Server versione 7.0 e SQL Server 2000 BULK INSERT supporta la conversione del tipo da string a decimal per le stringhe che rappresentano valori numerici con notazione scientifica.

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

[!NOTA] Nei file di formato i dati real sono rappresentati con il tipo SQLFLT4 e i dati float sono rappresentati con il tipo SQLFLT8. Per ulteriori informazioni sui file di formato XML, vedere Sintassi dello schema per file di formato XML. Per informazioni sui file di formato non XML, vedere Specifica del tipo di archiviazione di file tramite bcp.

Esempio di importazione di un valore numerico con notazione scientifica

Nell'esempio seguente viene utilizzata la tabella che segue:

CREATE TABLE t_float(c1 float, c2 decimal (5,4))

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

8.0000000000000002E-28.0000000000000002E-2

Non è tuttavia possibile importare i dati direttamente nella tabella t_float mediante BULK INSERT, poiché la seconda colonna della tabella, c2 utilizza il tipo di dati decimal. Pertanto, è necessario un file di formato. Il file di formato deve eseguire il mapping tra i dati float in notazione scientifica e il formato decimale della colonna c2.

Nel file di formato seguente viene utilizzato il tipo di dati SQLFLT8 per eseguire il mapping tra il secondo campo di dati e la seconda colonna:

<?xml version="1.0"?>

<BCPFORMAT xmlns="https://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 prova nella tabella di prova utilizzando questo file di formato con il nome di file C:\t_floatformat-c-xml.xml, eseguire l'istruzione Transact-SQL seguente:

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

Autorizzazioni

Sono richieste le autorizzazioni INSERT e ADMINISTER BULK OPERATIONS. È inoltre necessaria l'autorizzazione ALTER TABLE nei casi seguenti:

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

    [!NOTA] Per impostazione predefinita, i vincoli sono disattivati. Per verificare i vincoli in modo esplicito, utilizzare l'opzione CHECK_CONSTRAINTS.

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

    [!NOTA] Per impostazione predefinita, i trigger non vengono attivati. Per attivare i trigger in modo esplicito, utilizzare l'opzione FIRE_TRIGGERS.

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

Delega degli account di protezione (rappresentazione)

Se un utente di SQL Server esegue l'accesso con l'autenticazione di Windows, saranno disponibili per la lettura solo i file accessibili per l'account utente, indipendentemente dal profilo di protezione del processo di SQL Server.

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

Per correggere l'errore, utilizzare l'autenticazione di SQL Server e specificare un account di accesso di SQL Server che utilizza il profilo di protezione dell'account del processo di SQL Server oppure configurare Windows per consentire la delega degli account di protezione. Per informazioni sull'abilitazione di un account utente trusted per la delega, vedere la Guida di Windows.

Per ulteriori informazioni su questo e altri aspetti correlati alla protezione quando si utilizza BULK INSERT, vedere Importazione di dati per operazioni di massa utilizzando BULK INSERT o OPENROWSET(BULK...).

Esempi

A. Utilizzo di barre verticali per l'importazione di dati da un file

Nell'esempio seguente vengono importate informazioni dettagliate sugli ordini nella tabella AdventureWorks.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 AdventureWorks.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      )

B. Utilizzo dell'argomento FIRE_TRIGGERS

Nell'esempio seguente viene specificato l'argomento FIRE_TRIGGERS.

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

C. Utilizzo del 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 AdventureWorks.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

Esempi aggiuntivi

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

Vedere anche

Riferimento

OPENROWSET (Transact-SQL)
sp_tableoption (Transact-SQL)

Altre risorse

Preparazione dei dati per l'importazione o l'esportazione di massa
Formati di dati per l'importazione o l'esportazione
File di formato per l'importazione o l'esportazione di dati
Ottimizzazione delle prestazioni dell'importazione di massa
Informazioni sulle operazioni di importazione ed esportazione di massa
Scenari di importazione ed esportazione di massa di dati
Utilità bcp
Utilizzo delle regole di confronto SQL
Importazione di dati in parallelo tramite il blocco a livello di tabella

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche

Versione Cronologia

12 dicembre 2006

Contenuto modificato
  • Aggiornamento di una nota nella descrizione dell'argomento FIRSTROW.
  • Correzione della descrizione per CHECK_CONSTRAINTS per indicare che questa opzione consente di ignorare sia i vincoli CHECK che i vincoli FOREIGN KEY.

17 luglio 2006

Contenuto modificato
  • Aggiornamento della descrizione dell'argomento ORDER.

14 aprile 2006

Nuovo contenuto:
  • Aggiunta della sezione Esempi aggiuntivi.
Contenuto modificato
  • Correzione dell'ortografia dell'autorizzazione ADMINISTER BULK OPERATIONS.
  • Aggiornamento dei requisiti per l'autorizzazione ALTER TABLE.