OPENROWSET (Transact-SQL)

Si applica a: SQL Server Database SQL di Azure 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). È OPENROWSET possibile fare riferimento alla FROM funzione nella clausola 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.

Molti esempi in questo articolo si applicano solo a SQL Server. Dettagli e collegamenti a esempi simili su altre piattaforme:

• Database SQL di Azure supporta solo la lettura da Archiviazione BLOB di Azure.
• Per esempi su Istanza gestita di SQL di Azure, vedere Eseguire query sulle origini dati con OPENROWSET.
• Per informazioni ed esempi con pool SQL serverless in Azure Synapse, vedere Come usare OPENROWSET usando il pool SQL serverless in Azure Synapse Analytics.
• I pool SQL dedicati in Azure Synapse non supportano la OPENROWSET funzione.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

OPENROWSET la sintassi viene usata per eseguire query su origini dati esterne:

OPENROWSET
(  'provider_name'
    , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
    , {  [ catalog. ] [ schema. ] object | 'query' }
)

OPENROWSET(BULK) la sintassi viene usata per leggere i file esterni:

OPENROWSET( BULK 'data_file' ,
            { FORMATFILE = 'format_file_path' [ <bulk_options> ]
              | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
)

<bulk_options> ::=
   [ , DATASOURCE = 'data_source_name' ]

   -- bulk_options related to input file format
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ , FORMAT = { 'CSV' | 'PARQUET' | 'DELTA' } ]
   [ , FIELDQUOTE = 'quote_characters' ]
   [ , FORMATFILE = 'format_file_path' ]
   [ , FORMATFILE_DATA_SOURCE = 'data_source_name' ]

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

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

   [ , ROWS_PER_BATCH = rows_per_batch ]
   [ , ORDER ( { column [ ASC | DESC ] } [ , ...n ] ) [ UNIQUE ] ]

Argomenti

Argomenti comuni

'provider_name'

Stringa di caratteri che rappresenta il nome descrittivo (o PROGID) del provider OLE DB come 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 una determinata origine dati OLE DB. datasource è la DBPROP_INIT_DATASOURCE proprietà da passare all'interfaccia IDBProperties del provider per inizializzare il provider. In genere, questa stringa include il nome del file di database, il nome di un server di database o un nome che il provider riconosce 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 corrisponde al nome utente passato al provider OLE DB specificato. user_id specifica il contesto di sicurezza per la connessione e viene passato come DBPROP_AUTH_USERID proprietà per inizializzare il provider. user_id non può essere un nome di accesso di Microsoft Windows.

'password'

Costante stringa che rappresenta la password utente da passare al provider OLE DB. la password viene passata come DBPROP_AUTH_PASSWORD proprietà 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'

Un stringa di connessione specifico del provider passato come DBPROP_INIT_PROVIDERSTRING proprietà per inizializzare il 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 Initialization and Authorization Properties (Native Client OLE DB Provider).For a list of keywords that the SQL Server Native Client OLE DB provider recognizes, see Initialization and Authorization Properties (Native Client OLE DB Provider).

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 ed eseguita dal provider. L'istanza locale di SQL Server non elabora questa query, ma elabora i risultati delle query restituiti dal provider, una query pass-through. Le query pass-through sono utili quando vengono usate nei provider che non rendono disponibili i dati tabulari tramite nomi di tabella, ma solo tramite una lingua 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 Interfacce OLE DB (SQL Server Native Client).

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

Argomenti BULK

Usa il BULK provider del set di righe per OPENROWSET per leggere i dati da un file. In SQL Server è OPENROWSET possibile leggere da un file di dati senza caricare i dati in una tabella di destinazione. In questo modo è possibile usare OPENROWSET con un'istruzione di base SELECT .

Gli argomenti dell'opzione BULK consentono un controllo significativo sulla posizione in cui iniziare e terminare i dati di lettura, su come gestire gli errori e sul modo in cui i dati vengono interpretati. Ad esempio, è possibile specificare che il file di dati viene letto come set di righe a riga singola, a colonna singola di tipo varbinary, varchar o nvarchar. Il comportamento predefinito viene illustrato nelle descrizioni degli argomenti seguenti.

Per informazioni su come usare l'opzione BULK , vedere la sezione Osservazioni più avanti in questo articolo. Per informazioni sulle autorizzazioni richieste dall'opzione BULK , vedere la sezione Autorizzazioni più avanti in questo articolo.

Per informazioni sulla preparazione dei dati per l'importazione bulk, vedere Preparare i dati per l'esportazione o l'importazione bulk.

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 esempi, vedere Esempi di accesso in blocco ai dati in Archiviazione BLOB di Azure.

Opzioni di gestione degli errori BULK

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 aver corretto gli errori, è possibile caricare i dati.

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

ERRORFILE_DATA_SOURCE_NAME

A partire da SQL Server 2017 (14.x), questo argomento è un'origine dati esterna denominata che punta al percorso di archiviazione BLOB di Azure del file di errore che conterrà 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 = maximum_errors

Specifica il numero massimo di errori di sintassi o righe non conformi, come definito nel file di formato, che può verificarsi prima OPENROWSET di generare un'eccezione. Finché MAXERRORS non viene raggiunto, OPENROWSET ignora ogni riga non valida, non la carica e conta la riga non valida come un errore.

Il valore predefinito per maximum_errors è 10.

Opzioni di elaborazione dati BULK

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 è basato su 1.

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

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, ROWS_PER_BATCH è sconosciuto. Specificare ROWS_PER_BATCH = 0 equivale a omettere ROWS_PER_BATCH.

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

Hint facoltativo che specifica il modo in cui vengono ordinati i dati nel file di dati. Per impostazione predefinita, per l'operazione bulk si presume che il file di dati non sia ordinato. Le prestazioni possono migliorare se Query Optimizer può sfruttare l'ordine per generare un piano di query più efficiente. L'elenco seguente fornisce esempi per quando si specifica un ordinamento può essere utile:

• 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 FROM clausola di una query, in cui le colonne di ordinamento e join corrispondono.

UNIQUE

Specifica che il file di dati non contiene voci duplicate.

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

Quando si utilizzano gli alias di colonna, sono necessari ORDER alias di colonna. L'elenco di alias di colonna deve fare riferimento alla tabella derivata a cui si accede dalla BULK clausola . I nomi di colonna specificati nella ORDER clausola fanno riferimento a questo elenco di alias di colonna. Non è possibile specificare tipi valore di grandi dimensioni (varchar(max), nvarchar(max), varbinary(max)e xml) e tipi loB (large object) (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).

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 a colonna singola di tipo nvarchar(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 = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

Specifica la tabella codici dei dati contenuti nel file di dati. CODEPAGE è rilevante solo se i dati contengono colonne char, varchar o text con valori di carattere superiori a 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) 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. Le versioni importanti precedenti a SQL Server 2016 (13.x) non supportano la tabella codici 65001 (codifica UTF-8).

FORMAT = { 'CSV' | 'PARQUET' | 'DELTA' }

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

A partire da SQL Server 2022 (16.x), sono supportati sia i formati Parquet che Delta.

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

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 è quando SINGLE_CLOB, SINGLE_BLOBo SINGLE_NCLOB è specificato. In tal caso, il file di formato non è obbligatorio.

Per informazioni sui file di formato, vedere Usare un file di formato per l'importazione bulk di dati (SQL Server).For information about format files, see Use a format file to bulk import data (SQL Server).

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

FIELDQUOTE = 'field_quote'

A partire da SQL Server 2017 (14.x), questo argomento specifica un carattere usato come carattere di virgolette nel file CSV. Se non specificato, il carattere virgolette (") viene usato come carattere di virgolette come definito nello 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 sono impostate, il comportamento predefinito non consente l'accesso ad hoc.

Quando si accede a origini dati OLE DB remote, l'identità di accesso delle connessioni attendibili non viene delegata automaticamente dal server in cui il client è connesso al server su cui viene eseguita una 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 il catalogo e lo schema possono essere omessi quando 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.

OPENROWSET non accetta variabili per i relativi 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.

Usare OPENROWSET con l'opzione BULK

I miglioramenti transact-SQL seguenti supportano la OPENROWSET(BULK...) funzione :

• Una clausola FROM usata con SELECT può chiamare OPENROWSET(BULK...) anziché un nome di tabella. In questo modo, sono disponibili tutte le funzionalità di 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 viene specificato un elenco di alias di colonna, il file di formato deve avere 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 Usare BULK INSERT o OPENROWSET(BULK...) per importare dati in SQL Server.

• Quando l'opzione OPENROWSET BULK viene usata con un'istruzione INSERT , la BULK clausola supporta 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.

Importazione bulk di dati SQLCHAR, SQLNCHAR o SQLBINARY

OPENROWSET(BULK...) presuppone che, se non specificato, la lunghezza massima di SQLCHAR, SQLNCHARo SQLBINARY i dati non superino 8.000 byte. Se i dati importati si trovano in un campo dati LOB contenente qualsiasi oggetto varchar(max), nvarchar(max)o varbinary(max) che supera 8.000 byte, è necessario utilizzare un file di formato XML che definisce la lunghezza massima per il campo dati. Per specificare la lunghezza massima, modificare il file di formato dichiarando l'attributo MAX_LENGTH.

Esportazione o importazione in massa 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 oppure SQLVARYCHAR	I dati vengono inviati nella tabella codici client o nella tabella codici implicita dalle regole di confronto.
SQLNCHAR oppure SQLNVARCHAR	I dati vengono inviati in formato Unicode.
SQLBINARY oppure 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

In questa sezione vengono forniti esempi generali per illustrare come usare OPENROWSET.

R. Usare OPENROWSET con SELECT e il provider OLE DB di SQL Server Native Client

Si applica solo a: SQL Server.

SQL Server Native Client (spesso abbreviato SNAC) è stato rimosso da SQL Server 2022 (16.x) e da SQL Server Management Studio 19 (SSMS). Il provider OLE DB di SQL Server Native Client (SQLNCLI o SQLNCLI11) e il provider OLE DB legacy Microsoft per SQL Server (SQLOLEDB) non sono consigliati per lo sviluppo di nuove applicazioni. In futuro, passare al nuovo driver Microsoft OLE DB (MSOLEDBSQL) per SQL Server.

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. Usare il provider Microsoft OLE DB per Jet

Si applica solo a: SQL Server.

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

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

C. Usare OPENROWSET e un'altra tabella in un INNER JOIN

Si applica solo a: SQL Server.

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.

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;

D. Usare OPENROWSET per ESEGUIRE BULK INSERT i dati dei file in una colonna varbinary(max)

Si applica solo a: SQL Server.

Nell'esempio seguente viene creata una tabella di piccole dimensioni a scopo dimostrativo e vengono inseriti dati di file da un file denominato Text1.txt nella C: directory radice 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

E. Usare il provider OPENROWSET BULK con un file di formato per recuperare righe da un file di testo

Si applica solo a: SQL Server.

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

Questa query recupera i dati:

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

F. Specificare un file di formato e una tabella codici

Si applica solo a: SQL Server.

Nell'esempio seguente viene illustrato come usare contemporaneamente le opzioni del file di formato e della tabella codici.

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

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

Si applica solo a: SQL Server 2017 (14.x) e versioni successive.

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

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

Si applica solo a: SQL Server.

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'
);

I. Accedere ai dati da un file archiviato in Archiviazione BLOB di Azure

Si applica solo a: SQL Server 2017 (14.x) e versioni successive.

In SQL Server 2017 (14.x) e versioni successive, nell'esempio seguente viene usata 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 completiOPENROWSET, inclusa la configurazione delle credenziali e dell'origine dati esterna, vedere Esempi di accesso in blocco ai dati in Archiviazione BLOB di Azure.

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

L'esempio seguente illustra come usare il OPENROWSET comando per caricare dati da un file CSV in un percorso di archiviazione BLOB di Azure in cui è stata creata la 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***************';

-- Make sure that you don't have a leading ? in the 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 is not required if a blob is configured for public (anonymous) access!
    CREDENTIAL = MyAzureBlobStorageCredential
);

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;

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;

.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.

L'esempio seguente usa 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 Delta 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;

Altri esempi

Per altri esempi che illustrano l'uso INSERT...SELECT * FROM OPENROWSET(BULK...)di , vedere gli articoli seguenti:

• Esempi di importazione ed esportazione in blocco di documenti XML (SQL Server)
• Mantenere i valori Identity durante l'importazione in blocco dei dati (SQL Server)
• Mantenere i valori Null o i valori predefiniti durante un'importazione in blocco (SQL Server)
• Usare un file di formato per l'importazione in blocco dei dati (SQL Server)
• Usare il formato carattere per importare o esportare dati (SQL Server)
• Usare un file di formato per ignorare una colonna di una tabella (SQL Server)
• Usare un file di formato per escludere un campo dati (SQL Server)
• Usare un file di formato per eseguire il mapping tra le colonne di tabella e i campi del file di dati (SQL Server)
• Eseguire query sulle origini dati usando OPENROWSET in Istanza gestita di SQL di Azure

Contenuto correlato

• DELETE (Transact-SQL)
• Clausola FROM con JOIN, APPLY, PIVOT (Transact-SQL)
• Informazioni sull'importazione e l'esportazione in blocco di dati (SQL Server)
• INSERT (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)
• OPENQUERY (Transact-SQL)
• SELECT (Transact-SQL)
• sp_addlinkedserver (Transact-SQL)
• sp_serveroption (Transact-SQL)
• UPDATE (Transact-SQL)
• WHERE (Transact-SQL)