Condividi tramite

Usare BULK INSERT o OPENROWSET (BULK...) per importare dati in SQL Server

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azure

Questo articolo offre una panoramica di come usare l'istruzione Transact-SQL BULK INSERT e l'istruzione INSERT...SELECT * FROM OPENROWSET(BULK...) per importare in blocco dati da un file di dati in una tabella di SQL Server o di database SQL di Azure.

Questo articolo descrive anche le considerazioni sulla sicurezza per l'uso di BULK INSERT e OPENROWSET(BULK...), e l'uso di questi metodi per eseguire l'importazione massiva da un'origine dati remota.

Istruzione BULK INSERT

BULK INSERT carica i dati da un file di dati in una tabella. Questa funzionalità è simile a quella fornita dall'opzione in del bcp comando . Tuttavia, il file di dati viene letto dal processo di SQL Server. Per una descrizione della BULK INSERT sintassi, vedere BULK INSERT (Transact-SQL).

Esempi di BULK INSERT

Funzione OPENROWSET(BULK…)

Il provider del set di righe bulk viene accesso chiamando la funzione OPENROWSET e specificando l'opzione OPENROWSET. La OPENROWSET(BULK...) funzione consente di accedere ai dati remoti connettendosi a un'origine dati remota, ad esempio un file di dati, tramite un provider di dati.

Per importare dati in blocco, chiamare OPENROWSET(BULK...) da una clausola SELECT...FROM all'interno di un'istruzione INSERT.

La sintassi di base per l'importazione bulk dei dati è la seguente:

INSERT ... SELECT * FROM OPENROWSET(BULK...)

Se usato in un'istruzione INSERT , OPENROWSET(BULK...) supporta hint di tabella. Oltre ai normali hint di tabella, ad esempio TABLOCK, la BULK clausola può accettare gli hint di tabella specializzati seguenti:

  • IGNORE_CONSTRAINTS (ignora solo i vincoli CHECK)
  • IGNORE_TRIGGERS
  • KEEPDEFAULTS
  • KEEPIDENTITY

Per ulteriori informazioni, consultare Suggerimenti di tabella (Transact-SQL).

Per informazioni sugli usi aggiuntivi dell'opzione BULK, vedere OPENROWSET BULK (Transact-SQL).

Gli esempi delle istruzioni INSERT...SELECT * FROM OPENROWSET(BULK...)

Considerazioni relative alla sicurezza

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 BULK INSERT comando viene avviato da un account di accesso tramite l'autenticazione di SQL Server, la connessione ai dati viene effettuata usando il contesto di sicurezza dell'account di processo di SQL Server (l'account usato 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.

Si può, ad esempio, prendere in considerazione un utente che abbia eseguito l'accesso a un'istanza di SQL Server utilizzando l'autenticazione di Windows. Perché l'utente sia in grado di utilizzare BULK INSERT o OPENROWSET per importare dati da un file di dati in una tabella di SQL Server, è necessario che l'account utente disponga dell'accesso in lettura al file di dati. Con l'accesso al file di dati, l'utente può importare i dati dal file in una tabella anche se il processo di SQL Server non dispone dell'autorizzazione per accedere al file. Non è necessario che l'utente conceda l'autorizzazione di accesso al file al processo di SQL Server.

SQL Server e Microsoft Windows possono essere configurati in modo da abilitare un'istanza di SQL Server per connettersi a un'altra istanza di SQL Server trasferendo le credenziali di un utente di Windows autenticato. Questa configurazione è nota come rappresentazione o delega. La comprensione delle modalità di gestione della sicurezza da parte della versione di SQL Server ai fini della rappresentazione dell'utente costituisce un elemento importante quando si usa BULK INSERT o OPENROWSET. La rappresentazione dell’utente fa sì che i file di dati possano trovarsi su un computer diverso rispetto al processo di SQL Server o all'utente. Se, ad esempio, un utente nel Computer_A ha accesso a un file di dati presente nel Computer_B e la delega delle credenziali è stata impostata in modo corretto, l'utente potrà connettersi a un'istanza di SQL Server in esecuzione nel Computer_C, accedere al file di dati sul Computer_B e importare in blocco i dati da quel file in una tabella sul Computer_C.

Importazione bulk in SQL Server da un file di dati remoto

Per usare BULK INSERT o INSERT...SELECT * FROM OPENROWSET(BULK...) per eseguire l'importazione bulk di dati da un altro computer, il file di dati deve essere condiviso tra i due computer. Per specificare un file di dati condiviso, usare il nome UNC (Universal Naming Convention), che assume il formato generale . \\Servername\Sharename\Path\Filename

Inoltre, è necessario che all'account utilizzato per l'accesso al file di dati siano state concesse le autorizzazioni richieste per la lettura del file sul disco remoto.

Ad esempio, l'istruzione BULK INSERT seguente esegue l'importazione bulk dei dati nella tabella SalesOrderDetail del database AdventureWorks da un file di dati denominato newdata.txt. Tale file di dati è memorizzato in una cartella condivisa denominata \dailyorders e presente in una directory condivisa di rete denominata salesforce in un sistema denominato computer2.

BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';

Nota

Questa restrizione non si applica all'utilità bcp perché il client legge il file indipendentemente da SQL Server.

Importazione massiva da Archiviazione BLOB di Azure

Quando si importa da Archiviazione BLOB di Azure e i dati non sono pubblici (accesso anonimo), creare una CREATE DATABASE SCOPED CREDENTIAL basata su una chiave SAS crittografata con CREATE MASTER KEY e successivamente creare un'origine database esterna da usare nel BULK INSERT comando.

In alternativa, creare un CREATE DATABASE SCOPED CREDENTIAL in base a MANAGED IDENTITY per autorizzare le richieste di accesso ai dati in account di archiviazione non pubblici. Quando si usa MANAGED IDENTITY, Azure Storage deve concedere le autorizzazioni all'identità gestita dell’istanza aggiungendo il ruolo predefinito di Azure RBAC Collaboratore dati BLOB di Storage, che fornisce accesso in lettura/scrittura all'identità gestita per i contenitori di Azure Blob Storage 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, viene usata l'identità default dell'istanza gestita, ovvero l'identità gestita assegnata dall'utente primario o l'identità gestita assegnata dal sistema se non viene specificata l'identità gestita assegnata dall'utente.

Importante

L'identità gestita non è supportata nelle versioni di SQL Server precedenti a SQL Server 2025.

Nota

Non usare la transazione esplicita o viene visualizzato un errore 4861.

Uso di BULK INSERT

L'esempio seguente illustra come usare il comando BULK INSERT per caricare dati da un file CSV in un percorso di archiviazione Blob di Azure per cui hai creato una chiave SAS. La posizione di Archiviazione BLOB di Azure è configurata come origine dati esterna. Questa operazione richiede credenziali con ambito database che usano una firma di accesso condiviso crittografata con 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 BULK INSERT comando per caricare dati da un file CSV in un percorso di archiviazione BLOB di Azure usando 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

Il database SQL di Azure non supporta la lettura da file di Windows.

Usare OPENROWSET

L'esempio seguente illustra come usare il comando OPENROWSET per caricare dati da un file CSV in un percorso di archiviazione Blob di Azure per cui hai creato una chiave SAS. La posizione di Archiviazione BLOB di Azure è configurata come origine dati esterna. Questa operazione richiede credenziali con ambito database che usano una firma di accesso condiviso crittografata con 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!
);

INSERT INTO achievements with (TABLOCK) (id, description)
SELECT * FROM OPENROWSET(
   BULK  'csv/achievements.csv',
   DATA_SOURCE = 'MyAzureBlobStorage',
   FORMAT ='CSV',
   FORMATFILE='csv/achievements-c.xml',
   FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
    ) AS DataFile;

Importante

Il database SQL di Azure non supporta la lettura da file di Windows.

Contenuto correlato