OPENROWSET (Transact-SQL)

  • Articolo

Si applica a:SQL ServerDatabase SQL diAzure Istanza gestita di SQL di Azure

Include tutte le informazioni di connessione necessarie per l'accesso remoto ai dati da un'origine dati OLE DB. Si tratta di un metodo alternativo per l'accesso alle tabelle di un server collegato e corrisponde a un metodo ad hoc eseguito una sola volta per la connessione e l'accesso ai dati remoti tramite OLE DB. Per ottenere riferimenti più frequenti alle origini dati OLE DB, utilizzare server collegati. Per altre informazioni, vedere Server collegati (Motore di database). È possibile fare riferimento alla funzione OPENROWSET nella clausola FROM di una query come se fosse un nome di tabella. È anche possibile fare riferimento alla funzione OPENROWSET come tabella di destinazione di un'istruzioneINSERT, UPDATE o DELETE, a seconda delle funzionalità del provider OLE DB. Anche quando la query può restituire più set di risultati, la funzione OPENROWSET restituisce solo il primo set.

OPENROWSET supporta anche le operazioni bulk tramite un provider BULK predefinito che consente di leggere i dati da un file e restituirli come set di righe.

Nota

Questo articolo non si applica ad Azure Synapse Analytics.

Convenzioni di sintassi Transact-SQL

Sintassi

OPENROWSET
( { 'provider_name' 
    , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
    , {   <table_or_view> | 'query' }
   | BULK 'data_file' ,
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )

<table_or_view> ::= [ catalog. ] [ schema. ] object

<bulk_options> ::=

   [ , DATASOURCE = 'data_source_name' ]

   [ , ERRORFILE = 'file_name' ]
   [ , ERRORFILE_DATA_SOURCE = 'data_source_name' ]
   [ , MAXERRORS = maximum_errors ]

   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]
   [ , ROWS_PER_BATCH = rows_per_batch ]
   [ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ] ]
  
   -- bulk_options related to input file format
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ , FORMAT = 'CSV' ]
   [ , FIELDQUOTE = 'quote_characters']
   [ , FORMATFILE = 'format_file_path' ]
   [ , FORMATFILE_DATA_SOURCE = 'data_source_name' ]

Nota

Per visualizzare la sintassi Transact-SQL per SQL Server 2014 e versioni precedenti, vedere Documentazione delle versioni precedenti.

Argomenti

'provider_name'

Stringa di caratteri che rappresenta il nome descrittivo (o PROGID) del provider OLE DB specificato nel Registro di sistema. provider_name non ha un valore predefinito. Sono esempi di nomi di provider Microsoft.Jet.OLEDB.4.0, SQLNCLI o MSDASQL.

'datasource'

Costante stringa che corrisponde a un'origine dati OLE DB specifica. datasource è la proprietà DBPROP_INIT_DATASOURCE da passare all'interfaccia IDBProperties del provider per l'inizializzazione di quest'ultimo. In genere questa stringa include il nome del file di database, il nome di un server di database o un nome riconosciuto dal provider per individuare il database o i database. L'origine dati può essere il percorso di file C:\SAMPLES\Northwind.mdb' per il provider Microsoft.Jet.OLEDB.4.0 o la stringa di connessione Server=Seattle1;Trusted_Connection=yes; per il provider SQLNCLI.

'user_id'

Costante stringa che rappresenta il nome utente passato al provider OLE DB specificato. user_id consente di specificare il contesto di sicurezza per la connessione e viene passato come proprietà DBPROP_AUTH_USERID per l'inizializzazione del provider. user_id non può essere un ID di accesso di Microsoft Windows.

'password'

Costante stringa che rappresenta la password utente da passare al provider OLE DB. password viene passato come proprietà DBPROP_AUTH_PASSWORD durante l'inizializzazione del provider. password non può essere una password di Microsoft Windows.

SELECT a.*
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                   'C:\SAMPLES\Northwind.mdb';
                   'admin';
                   'password',
                   Customers) AS a;

'provider_string'

Stringa di connessione specifica del provider passata come proprietà DBPROP_INIT_PROVIDERSTRING per l'inizializzazione del provider OLE DB. In provider_string sono incluse in genere tutte le informazioni di connessione necessarie per inizializzare il provider. Per un elenco di parole chiave riconosciute dal provider OLE DB di SQL Server Native Client, vedere Proprietà di inizializzazione e di autorizzazione.

SELECT d.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
                            Department) AS d;

<table_or_view>

Tabella o vista remota che contiene i dati che devono essere letti da OPENROWSET. Può essere un oggetto con nome in tre parti con i componenti seguenti:

  • catalogo (facoltativo) - Nome del catalogo o del database contenente l'oggetto specificato.
  • schema (facoltativo) - Nome dello schema o del proprietario dell'oggetto specificato.
  • oggetto - Nome dell'oggetto che identifica in modo univoco l'oggetto da usare.
SELECT d.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
                 AdventureWorks2022.HumanResources.Department) AS d;

'query'

Costante stringa inviata al provider ed eseguita da questo. L'istanza locale di SQL Server non elabora questa query, ma i risultati della query restituiti dal provider (query pass-through). Le query pass-through risultano utili quando vengono utilizzate in provider che non espongono i dati tabulari tramite i nomi di tabella, ma solo attraverso un linguaggio di comando. Le query pass-through sono supportate nel server remoto, a condizione che il provider di query supporti l'oggetto OLE DB Command e le relative interfacce obbligatorie. Per altre informazioni, vedere Informazioni di riferimento di SQL Server Native Client (OLE DB).

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT TOP 10 GroupName, Name
     FROM AdventureWorks2022.HumanResources.Department') AS a;

BULK

Utilizza il provider BULK per set di righe per OPENROWSET per leggere i dati da un file. In SQL Server, OPENROWSET è in grado di leggere da un file di dati senza caricare i dati in una tabella di destinazione. Ciò consente di utilizzare OPENROWSET con un'istruzione SELECT semplice.

Importante

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

Gli argomenti dell'opzione BULK consentono un controllo significativo su dove iniziare e terminare la lettura dei dati, come gestire gli errori e come interpretare i dati. È ad esempio possibile specificare che il file di dati deve essere letto come riga singola, set di righe a colonna singola di tipo varbinary, varchar o nvarchar. Il comportamento predefinito viene illustrato nelle descrizioni degli argomenti seguenti.

Per informazioni sull'utilizzo dell'opzione BULK, vedere la sezione "Osservazioni" di seguito in questo argomento. Per informazioni sulle autorizzazioni necessarie per l'opzione BULK, vedere la sezione "Autorizzazioni" di seguito in questo argomento.

Nota

Quando utilizzata per importare i dati con il modello di recupero con registrazione completa, OPENROWSET (BULK ...) non ottimizza la registrazione.

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

BULK 'data_file'

Percorso completo del file di dati i cui dati devono essere copiati nella tabella di destinazione.

SELECT * FROM OPENROWSET(
   BULK 'C:\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB) AS DATA;

A partire da SQL Server 2017 (14.x), il file specificato in data_file può essere presente in Archiviazione BLOB di Azure. Per alcuni esempi, vedere Esempi di accesso bulk ai dati nell'archiviazione BLOB di Azure.

Importante

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

Opzioni di gestione degli errori BULK

ERRORFILE

ERRORFILE='file_name' 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 di errori viene creato all'inizio dell'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 fornisce informazioni di diagnostica. Dopo la correzione degli errori, i dati possono essere caricati.

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

ERRORFILE_DATA_SOURCE_NAME

A partire da SQL Server 2017 (14.x), è un'origine dati esterna denominata che punta al percorso di archiviazione BLOB di Azure del file di errore che conterrà gli errori rilevati durante l'importazione. L'origine dati esterna deve essere creata utilizzando .TYPE = BLOB_STORAGE Per altre informazioni, vedere CREATE EXTERNAL DATA SOURCE.

MAXERRORS

MAXERRORS =maximum_errors specifica il numero massimo di errori di sintassi o righe non conformi, definite nel file di formato, che possono verificarsi prima che OPENROWSET generi un'eccezione. Fino al raggiungimento di MAXERRORS, OPENROWSET ignora ogni riga non conforme, non caricandola, e conteggia la riga non conforme come un errore.

Il valore predefinito per maximum_errors è 10.

Nota

MAX_ERRORS non si applica ai vincoli CHECK o alla conversione dei tipi di dati money e bigint.

Opzioni di elaborazione dei dati BULK

FIRSTROW

FIRSTROW =first_row

Specifica il numero della prima riga da caricare. Il valore predefinito è 1. Questo valore indica la prima riga nel file di dati specificato. I numeri di riga sono determinati dal conteggio dei caratteri di terminazione. FIRSTROW è in base 1.

LASTROW

LASTROW =last_row

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

ROWS_PER_BATCH

ROWS_PER_BATCH =rows_per_batch

Specifica il numero approssimativo di righe di dati nel file di dati. Questo valore deve essere dello stesso ordine del numero effettivo di righe.

OPENROWSET importa sempre un file di dati come batch singolo. Se tuttavia si specifica rows_per_batch con un valore > 0, Query Processor usa il valore di rows_per_batch come hint per l'allocazione delle risorse nel piano di query.

Per impostazione predefinita, il valore ROWS_PER_BATCH è sconosciuto. La specifica di ROWS_PER_BATCH = 0 equivale all'omissione di ROWS_PER_BATCH.

ORDER

ORDER ( { column [ ASC | DESC ] } [ ,... n ] [ UNIQUE ] ) Suggerimento facoltativo che specifica la modalità di ordinamento dei dati nel file. Per impostazione predefinita, per l'operazione bulk si presume che il file di dati non sia ordinato. Se l'ordine specificato può essere sfruttato da Query Optimizer per generare un piano di query più efficiente, le prestazioni possono migliorare. Di seguito vengono riportati alcuni esempi di situazioni in cui può essere utile specificare l'ordinamento:

  • Inserimento di righe in una tabella con un indice cluster, in cui i dati del set di righe sono ordinati in base alla chiave dell'indice cluster.
  • Unione del set di righe con un'altra tabella, in cui le colonne di ordinamento e di join corrispondono.
  • Aggregazione dei dati del set di righe tramite le colonne dell'ordinamento.
  • Utilizzo del set di righe come tabella di origine nella clausola FROM di una query, in cui le colonne di ordinamento e di join corrispondono.
UNIQUE

UNIQUE specifica che il file di dati non ha voci duplicate.

Se le righe effettive del file di dati non sono ordinate in base all'ordine specificato o se l'hint UNIQUE viene specificato e sono presenti chiavi duplicate, viene restituito un errore.

Gli alias di colonna sono richiesti quando si utilizza ORDER. L'elenco di alias di colonna deve fare riferimento alla tabella derivata a cui è possibile accedere tramite la clausola BULK. I nomi di colonna specificati nella clausola ORDER si riferiscono a questo elenco di alias di colonna. Non è possibile specificare colonne di tipi valore di grandi dimensioni (varchar(max), nvarchar(max), varbinary(max) e xml) e di tipi Large Object (LOB) (text, ntext e image).

SINGLE_BLOB

Restituisce il contenuto di data_file come set di righe a riga singola e a colonna singola di tipo varbinary(max).

Importante

Per l'importazione di dati XML è consigliabile utilizzare solo l'opzione SINGLE_BLOB anziché SINGLE_CLOB e SINGLE_NCLOB, in quanto solo SINGLE_BLOB supporta tutti i tipi di conversione di codifica di Windows.

SINGLE_CLOB

Leggendo data_file come ASCII, restituisce il contenuto come set di righe a riga singola e colonna singola di tipo varchar(max), usando le regole di confronto del database corrente.

SINGLE_NCLOB

Leggendo data_file come UNICODE, restituisce il contenuto come set di righe a riga singola e colonna singola di tipo varchar(max), usando le regole di confronto del database corrente.

SELECT *
   FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_NCLOB) AS Document;

Opzioni di formato del file di input BULK

CODEPAGE

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.

Importante

CODEPAGE non è un'opzione supportata in Linux.

Nota

È consigliabile specificare un nome di regole di confronto per ogni colonna in un file di formato tranne quando si vuole assegnare all'opzione 65001 la priorità sulla specifica delle regole di confronto o della tabella codici.

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) Converte le colonne con tipo di dati char, varchar o text dalla tabella codici OEM di sistema a quella di SQL Server.
RAW Non vengono eseguite conversioni tra tabelle codici. Si tratta dell'opzione più rapida.
code_page Indica la tabella codici di origine in cui vengono codificati i dati di tipo carattere del file di dati, ad esempio 850.

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

FORMAT= 'CSV'

A partire da SQL Server 2017 (14.x), specifica un file di valori delimitati da virgole conforme allo standard RFC 4180 .

SELECT *
FROM OPENROWSET(BULK N'D:\XChange\test-csv.csv',
    FORMATFILE = N'D:\XChange\test-csv.fmt',
    FIRSTROW=2,
    FORMAT='CSV') AS cars;
FORMATFILE

FORMATFILE ='format_file_path' Specifica il percorso completo di un file di formato. SQL Server supporta due tipi di file di formato, ovvero XML e non XML.

Un file di formato è necessario per definire i tipi di colonna nel set di risultati. L'unica eccezione si verifica quando viene specificato SINGLE_CLOB, SINGLE_BLOB o SINGLE_NCLOB. In questo caso, il file di formato non è necessario.

Per informazioni sui file di formato, vedere Usare un file di formato per l'importazione bulk dei dati (SQL Server).

A partire da SQL Server 2017 (14.x), il format_file_path può trovarsi in Archiviazione BLOB di Azure. Per alcuni esempi, vedere Esempi di accesso bulk ai dati nell'archiviazione BLOB di Azure.

FIELDQUOTE

FIELDQUOTE= 'field_quote'

A partire da SQL Server 2017 (14.x), specifica un carattere che verrà usato come carattere di virgolette nel file CSV. Se non viene specificato alcun carattere, viene usato il carattere virgolette (") in base alla definizione dello standard RFC 4180.

Osservazioni:

È possibile usare OPENROWSET per accedere ai dati remoti da origini dati OLE DB solo se l'opzione del Registro di sistema DisallowAdhocAccess è impostata esplicitamente su 0 per il provider specificato e l'opzione di configurazione avanzata Ad Hoc Distributed Queries è abilitata. Quando queste opzioni non vengono impostate, il comportamento predefinito non consente l'accesso ad hoc.

Quando si accede alle origini dati OLE DB remote, l'identità dell'account di accesso delle connessioni trusted non viene delegata automaticamente dal server in cui il client è connesso al server su cui viene eseguita la query. È necessario configurare la delega dell'autenticazione.

Se il provider OLE DB supporta più cataloghi e schemi nell'origine dati specificata, è necessario specificare i nomi di catalogo e di schema. I valori per catalog e )schema possono essere omessi se il provider OLE DB non li supporta. Se il provider supporta solo nomi di schema, è necessario specificare un nome composto da due parti nel formato schema.oggetto. Se il provider supporta solo nomi di catalogo, è necessario specificare un nome composto da tre parti nel formato catalogo.schema.oggetto. È necessario specificare nomi composti da tre parti per le query pass-through che usano il provider OLE DB di SQL Server Native Client. Per altre informazioni, vedere Convenzioni di sintassi Transact-SQL.

La funzione OPENROWSET non accetta variabili come argomenti.

Qualsiasi chiamata a OPENDATASOURCE, OPENQUERY r OPENROWSET nella clausola FROM viene valutata separatamente e indipendentemente da qualsiasi altra chiamata a queste funzioni usate come destinazione dell'aggiornamento, anche se alle due chiamate vengono forniti argomenti identici. In particolare, le condizioni di filtro o join applicate al risultato di una di tali chiamate non hanno effetto sui risultati dell'altra.

Utilizzo di OPENROWSET con l'opzione BULK

I miglioramenti seguenti apportati a Transact-SQL offrono il supporto per la funzione OPENROWSET(BULK…):

  • Una clausola FROM usata con SELECT può chiamare OPENROWSET(BULK...) anziché un nome di tabella. In questo modo, sono disponibili tutte le funzionalità dell'istruzione SELECT.

    OPENROWSET con l'opzione BULK richiede un nome di correlazione, noto anche come alias o variabile di intervallo, nella clausola FROM. È possibile specificare alias di colonne. Se non è specificato un elenco di alias di colonna, il file di formato deve contenere nomi di colonna. Se si specificano gli alias di colonna, i nomi di colonna nel file di formato vengono sostituiti, ad esempio:

    • FROM OPENROWSET(BULK...) AS table_alias
    • FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)

    Importante

    Se non si aggiunge , AS <table_alias> verrà restituito l'errore: Msg 491, Level 16, State 1, Line 20 È necessario specificare il nome di correlazione per il set di righe bulk nella clausola from.

  • Un'istruzione SELECT...FROM OPENROWSET(BULK...) consente di eseguire query direttamente sui dati in un file, senza importare i dati in una tabella. Le istruzioni SELECT...FROM OPENROWSET(BULK...) consentono anche di elencare alias di colonna bulk usando un file di formato per specificare nomi di colonna e tipi di dati.

  • L'uso di OPENROWSET(BULK...) come tabella di origine in un'istruzione INSERT o MERGE consente di eseguire l'importazione bulk di dati da un file di dati in una tabella di SQL Server. Per altre informazioni, vedere Importare dati per operazioni bulk con BULK INSERT o OPENROWSET(BULK...) (SQL Server).

  • Quando l'opzione OPENROWSET BULK viene usata con un'istruzione INSERT, la clausola BULK supporta gli hint di tabella. Oltre agli hint di tabella normali, ad esempio TABLOCK, la clausola BULK può accettare gli hint di tabella specializzati seguenti: IGNORE_CONSTRAINTS (ignora solo i vincoli CHECK e FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTS e KEEPIDENTITY. Per ulteriori informazioni, vedere Hint di tabella (Transact-SQL).

    Per informazioni su come usare le istruzioni INSERT...SELECT * FROM OPENROWSET(BULK...), vedere Importazione ed esportazione bulk di dati (SQL Server). Per informazioni sui casi in cui le operazioni di inserimento di righe eseguite durante l'importazione in blocco vengono registrate nel log delle transazioni, vedere Prerequisiti per la registrazione minima nell'importazione in blocco.

Nota

Quando si usa OPENROWSET, è importante comprendere il modo in cui la rappresentazione viene gestita da SQL Server. Per considerazioni sulla sicurezza, vedere Importazione di dati per operazioni bulk con BULK INSERT o OPENROWSET(BULK...) (SQL Server).

Importazione bulk di dati SQLCHAR, SQLNCHAR o SQLBINARY

OPENROWSET(BULK...) presuppone che, se non è specificata, la lunghezza massima dei dati SQLCHAR, SQLNCHAR o SQLBINARY non supera 8000 byte. Se i dati in corso di importazione sono in un campo dati di tipo LOB contenente un oggetto varchar(max), nvarchar(max) o varbinary(max) che supera 8000 byte, è necessario usare un file di formato XML che definisca la lunghezza massima per il campo dati. Per specificare la lunghezza massima, modificare il file di formato dichiarando l'attributo MAX_LENGTH.

Nota

Un file di formato generato automaticamente non specifica la lunghezza o la lunghezza massima per un campo di tipo LOB. Tuttavia, è possibile modificare un file di formato e specificare la lunghezza o la lunghezza massima manualmente.

Esportazione o importazione bulk di documenti SQLXML

Per 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 SQLVARYCHAR I dati vengono inviati nella tabella codici del client o nella tabella codici implicita delle regole di confronto.
SQLNCHAR o SQLNVARCHAR I dati vengono inviati in formato Unicode.
SQLBINARY o SQLVARYBIN I dati vengono inviati senza conversione.

Autorizzazioni

Le autorizzazioni OPENROWSET sono determinate dalle autorizzazioni del nome utente che viene passato al provider OLE DB. L'uso dell'opzione BULK richiede l'autorizzazione ADMINISTER BULK OPERATIONS o ADMINISTER DATABASE BULK OPERATIONS.

Esempi

A. Utilizzo di OPENROWSET con SELECT e il provider OLE DB di SQL Server Native Client

Nell'esempio seguente viene usato il provider OLE DB di SQL Server Native Client per accedere alla HumanResources.Department tabella nel AdventureWorks2022 database nel server Seattle1remoto . (L'utilizzo di SQLNCLI e SQL Server reindirizza alla versione più recente del provider OLE DB per SQL Server Native Client.) Viene usata un'istruzione SELECT per definire il set di righe restituito. La stringa del provider contiene le parole chiave Server e Trusted_Connection. Queste parole chiave sono riconosciute dal provider OLE DB di SQL Server Native Client.

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks2022.HumanResources.Department
      ORDER BY GroupName, Name') AS a;

B. Utilizzo del provider Microsoft OLE DB per Jet

Nell'esempio seguente viene ottenuto l'accesso alla tabella Customers del database Northwind di Microsoft Access tramite il provider Microsoft OLE DB per Jet.

Nota

Nell'esempio si presuppone che Access sia installato. Per eseguire questo esempio, è necessario installare il database Northwind.

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
      'admin';'',Customers);

Importante

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

C. Utilizzo di OPENROWSET e di un'altra tabella in un INNER JOIN

Nell'esempio seguente vengono selezionati tutti i dati della tabella Customers dell'istanza locale del database Northwind di SQL Server e della tabella Orders del database Northwind di Access archiviato nello stesso computer.

Nota

Nell'esempio si presuppone che Access sia installato. Per eseguire questo esempio, è necessario installare il database Northwind.

USE Northwind;
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
   INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)
   AS o
   ON c.CustomerID = o.CustomerID ;

Importante

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

D. Utilizzo di OPENROWSET per eseguire un inserimento bulk dei dati del file in una colonna varbinary(max).

Nell'esempio seguente viene creata una tabella di piccole dimensioni a titolo dimostrativo e vengono quindi inseriti i dati di un file denominato Text1.txt archiviato nella directory radice C: in una colonna varbinary(max).

CREATE TABLE myTable(FileName NVARCHAR(60),
  FileType NVARCHAR(60), Document VARBINARY(max));
GO

INSERT INTO myTable(FileName, FileType, Document)
   SELECT
      'Text1.txt' AS FileName
      , '.txt' AS FileType
      , *
   FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;
GO

Importante

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

E. Utilizzo del provider OPENROWSET BULK con un file di formato per recuperare le righe da un file di testo

Nell'esempio seguente viene utilizzato un file di formato per recuperare le righe da un file di testo delimitato da tabulazioni, values.txt contenente i dati seguenti:

1     Data Item 1
2     Data Item 2
3     Data Item 3

Il file di formato, values.fmt, descrive le colonne in values.txt:

9.0
2  
1  SQLCHAR  0  10 "\t"        1  ID                      SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"      2  Description        SQL_Latin1_General_Cp437_BIN

Di seguito è illustrata la query che recupera tali dati:

SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',
   FORMATFILE = 'c:\test\values.fmt') AS a;

Importante

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

F. Indicazione di un file di formato e di una tabella codici

L'esempio seguente illustra come usare sia il file di formato che la tabella codici contemporaneamente.

INSERT INTO MyTable SELECT a.* FROM
OPENROWSET (BULK N'D:\data.csv', FORMATFILE =
    'D:\format_no_collation.txt', CODEPAGE = '65001') AS a;

G. Accesso ai dati da un file CSV con un file di formato

A partire da SQL Server 2017 (14.x).

SELECT *
FROM OPENROWSET(BULK N'D:\XChange\test-csv.csv',
    FORMATFILE = N'D:\XChange\test-csv.fmt',
    FIRSTROW=2,
    FORMAT='CSV') AS cars;

Importante

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

H. Accesso ai dati da un file CSV senza un file di formato

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14.CTP1_1\MSSQL\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB) AS DATA;

SELECT *
FROM OPENROWSET
   (  'MSDASQL'
     ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}'
     ,'select * from E:\Tlog\TerritoryData.csv')
;

Importante

  • Il driver ODBC deve essere a 64 bit. Per verificare che questo requisito sia soddisfatto, aprire la scheda Driver dell'applicazione Origini dati ODBC in Windows. È presente un Microsoft Text Driver (*.txt, *.csv) a 32 bit che non funziona con una versione a 64 bit di sqlservr.exe.
  • Database SQL di Azure supporta solo la lettura da Archiviazione BLOB di Azure.

I. Accesso ai dati da un file archiviato nell'archiviazione BLOB di Azure

A partire da SQL Server 2017 (14.x), l'esempio seguente usa un'origine dati esterna che punta a un contenitore in un account di archiviazione di Azure e a credenziali con ambito database create per una firma di accesso condiviso.

SELECT * FROM OPENROWSET(
   BULK 'inv-2017-01-19.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   SINGLE_CLOB) AS DataFile;

Per esempi di OPENROWSET 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.

J. Importazione in una tabella da un file archiviato in Archiviazione BLOB di Azure

L'esempio seguente illustra come usare il comando OPENROWSET per caricare dati da un file CSV in una posizione di Archiviazione BLOB di Azure in cui è stata creata una chiave di firma di accesso condiviso. 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/curriculum'
          , 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

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

K. Usare un'identità gestita per un'origine esterna

L'esempio seguente crea una credenziale usando un'identità gestita, crea un'origine esterna e quindi carica i dati da un file CSV ospitato nell'origine esterna.

Creare prima di tutto le credenziali e specificare l'archiviazione BLOB come origine esterna:

CREATE DATABASE SCOPED CREDENTIAL sampletestcred WITH IDENTITY = 'MANAGED IDENTITY';

CREATE EXTERNAL DATA SOURCE SampleSource
WITH (TYPE = BLOB_STORAGE,
LOCATION = 'https://****************.blob.core.windows.net/curriculum',
CREDENTIAL = sampletestcred

Caricare quindi i dati dal file CSV ospitato nell'archivio BLOB:

SELECT * FROM OPENROWSET(
BULK 'Test - Copy.csv',
DATA_SOURCE = 'SampleSource',
SINGLE_CLOB
) as test

Importante

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

.L Usare OPENROWSET per accedere a diversi file parquet usando l'archiviazione oggetti compatibile con S3

Si applica a: SQL Server 2022 (16.x) e versioni successive.

Nell'esempio seguente viene usato l'accesso a diversi file Parquet da un percorso diverso, tutti archiviati nell'archivio oggetti compatibile con S3:


CREATE DATABASE SCOPED CREDENTIAL s3_dsc
WITH IDENTITY = 'S3 Access Key',
SECRET = 'contosoadmin:contosopwd'
GO

CREATE EXTERNAL DATA SOURCE s3_eds
WITH
(
 LOCATION = 's3://10.199.40.235:9000/movies'
,CREDENTIAL = s3_dsc
)
GO

SELECT *
FROM  
    OPENROWSET(
        BULK (
            '/decades/1950s/*.parquet',
			'/decades/1960s/*.parquet',
			'/decades/1970s/*.parquet'),
        FORMAT='PARQUET'
		,DATA_SOURCE = 's3_eds'
    )
AS [data]

M. Usare OPENROWSET per accedere a diversi file differenziali da Azure Data Lake Gen2

Si applica a: SQL Server 2022 (16.x) e versioni successive.

In questo esempio il contenitore della tabella dati è denominato Contosoe si trova in un account di archiviazione di Azure Data Lake Gen2.

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc   
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', 
SECRET = '<SAS Token>';  

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH
(
 LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net'
,CREDENTIAL = delta_storage_dsc 
);

SELECT  * 
FROM    OPENROWSET
        (   BULK '/Contoso'
        ,   FORMAT = 'DELTA'
        ,   DATA_SOURCE = 'Delta_ED'
        ) as [result];

Esempi aggiuntivi

Per esempi aggiuntivi relativi all'uso di INSERT...SELECT * FROM OPENROWSET(BULK...), vedere gli argomenti seguenti:

Passaggi successivi