OPENROWSET (Transact-SQL)
S’applique à : SQL Server
Azure SQL Database
Azure SQL Managed Instance
Inclut toutes les informations de connexion exigées pour accéder à des données distantes à partir d'une source de données OLE DB. Cette méthode est une autre façon d'accéder à des tables dans un serveur lié et constitue une méthode efficace pour vous connecter et accéder à des données distantes en utilisant OLE DB. Pour faire des références plus fréquentes à des sources de données OLE DB, utilisez plutôt des serveurs liés. Pour plus d’informations, consultez Serveurs liés (moteur de base de données). La OPENROWSET
fonction peut être référencée dans la FROM
clause d’une requête comme s’il s’agissait d’un nom de table. La fonction OPENROWSET
peut également être référencée comme table cible d’une instruction INSERT
, UPDATE
ou DELETE
, en fonction des capacités du fournisseur OLE DB. Bien que la requête puisse retourner plusieurs jeux de résultats, OPENROWSET
ne retourne que le premier.
OPENROWSET
prend également en charge les opérations en bloc par l’intermédiaire d’un fournisseur BULK
intégré qui permet de lire les données d’un fichier et de les retourner sous la forme d’un ensemble de lignes.
De nombreux exemples de cet article s’appliquent uniquement à SQL Server. Détails et liens vers des exemples similaires sur d’autres plateformes :
- Azure SQL Database prend uniquement en charge la lecture à partir du Stockage Blob Azure.
- Pour obtenir des exemples sur Azure SQL Managed Instance, consultez Les sources de données de requête à l’aide d’OPENROWSET.
- Pour plus d’informations et d’exemples avec des pools SQL serverless dans Azure Synapse, consultez Comment utiliser OPENROWSET à l’aide d’un pool SQL serverless dans Azure Synapse Analytique.
- Les pools SQL dédiés dans Azure Synapse ne prennent pas en charge la
OPENROWSET
fonction.
Conventions de la syntaxe Transact-SQL
Syntaxe
OPENROWSET
la syntaxe est utilisée pour interroger des sources de données externes :
OPENROWSET
( 'provider_name'
, { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
, { [ catalog. ] [ schema. ] object | 'query' }
)
OPENROWSET(BULK)
la syntaxe est utilisée pour lire des fichiers externes :
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 ] ]
Remarque
Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 (12.x) et versions antérieures, consultez la Documentation sur les versions antérieures.
Arguments
Arguments communs
'provider_name'
Chaîne de caractères qui représente le nom convivial (ou PROGID
) du fournisseur OLE DB tel que spécifié dans le Registre. provider_name n’a aucune valeur par défaut. Des exemples de noms de fournisseurs sont Microsoft.Jet.OLEDB.4.0
, SQLNCLI
ou MSDASQL
.
'datasource'
Constante de chaîne qui correspond à une source de données OLE DB particulière. la source de données est la DBPROP_INIT_DATASOURCE
propriété à transmettre à l’interface IDBProperties
du fournisseur pour initialiser le fournisseur. En règle générale, cette chaîne inclut le nom du fichier de base de données, le nom d’un serveur de base de données ou un nom que le fournisseur comprend pour localiser la base de données ou les bases de données.
La source de données peut être le chemin de fichier C:\SAMPLES\Northwind.mdb'
pour le fournisseur Microsoft.Jet.OLEDB.4.0
ou la chaîne de connexion Server=Seattle1;Trusted_Connection=yes;
pour le fournisseur SQLNCLI
.
'user_id'
Constante de chaîne qui correspond au nom d’utilisateur passé au fournisseur OLE DB spécifié. user_id spécifie le contexte de sécurité de la connexion et est transmis en tant que DBPROP_AUTH_USERID
propriété pour initialiser le fournisseur. user_id ne peut pas être un nom de connexion Microsoft Windows.
'password'
Constante de chaîne qui correspond au mot de passe utilisateur à passer au fournisseur OLE DB. le mot de passe est transmis en tant que propriété lors de l’initialisation DBPROP_AUTH_PASSWORD
du fournisseur. le mot de passe ne peut pas être un mot de passe Microsoft Windows.
SELECT a.* FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'C:\SAMPLES\Northwind.mdb';
'admin';
'password',
Customers
) AS a;
'provider_string'
Un chaîne de connexion spécifique au fournisseur qui est passé en tant que DBPROP_INIT_PROVIDERSTRING
propriété pour initialiser le fournisseur OLE DB. En général, provider_string encapsule toutes les informations de connexion nécessaires à l’initialisation du fournisseur. Pour obtenir la liste des mots clés que le fournisseur OLE DB SQL Server Native Client reconnaît, consultez Les propriétés d’initialisation et d’autorisation (fournisseur OLE DB Native Client) .
SELECT d.* FROM OPENROWSET(
'SQLNCLI',
'Server=Seattle1;Trusted_Connection=yes;',
Department
) AS d;
<table_or_view>
Table ou vue distante contenant les données devant être lues par OPENROWSET
. Il peut s’agir d’un objet au nom en trois parties avec les composants suivants :
- catalog (facultatif) : nom du catalogue ou de la base de données où réside l’objet spécifié.
- schema (facultatif) : nom du propriétaire du schéma ou de l’objet pour l’objet spécifié.
- object : nom d’objet qui identifie de façon unique l’objet à manipuler.
SELECT d.* FROM OPENROWSET(
'SQLNCLI',
'Server=Seattle1;Trusted_Connection=yes;',
AdventureWorks2022.HumanResources.Department
) AS d;
'query'
Constante de chaîne envoyée et exécutée par le fournisseur. L’instance locale de SQL Server ne traite pas cette requête, mais traite les résultats de requête retournés par le fournisseur, une requête directe. Les requêtes directes sont utiles lorsqu’elles sont utilisées sur les fournisseurs qui ne rendent pas disponibles leurs données tabulaires par le biais de noms de tables, mais uniquement par le biais d’un langage de commande. Les requêtes directes sont prises en charge sur le serveur distant à condition que le fournisseur de requêtes prenne en charge l’objet OLE DB Command et ses interfaces obligatoires. Pour plus d’informations, consultez les interfaces 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;
Arguments BULK
Utilise le fournisseur d’ensembles BULK
de lignes pour OPENROWSET
lire des données à partir d’un fichier. Dans SQL Server, OPENROWSET
peut lire à partir d’un fichier de données sans charger les données dans une table cible. Cela vous permet d’utiliser OPENROWSET
une instruction de base SELECT
.
Important
Azure SQL Database prend uniquement en charge la lecture à partir du Stockage Blob Azure.
Les arguments de l’option BULK
permettent un contrôle significatif sur l’endroit où commencer et terminer la lecture des données, comment traiter les erreurs et comment les données sont interprétées. Par exemple, vous pouvez spécifier que le fichier de données est lu sous la forme d’un ensemble de lignes à une seule ligne de type varbinary, varchar ou nvarchar. Le comportement par défaut est indiqué dans les descriptions des arguments ci-dessous.
Pour plus d’informations sur l’utilisation de l’option BULK
, consultez la section Remarques plus loin dans cet article. Pour plus d’informations sur les autorisations requises par l’option BULK
, consultez la section Autorisations , plus loin dans cet article.
Remarque
Lorsqu’il est utilisé pour importer des données avec le modèle de récupération complète, OPENROWSET (BULK ...)
n’optimise pas la journalisation.
Pour plus d’informations sur la préparation des données pour l’importation en bloc, consultez Préparer des données pour l’exportation ou l’importation en bloc.
BULK 'data_file'
Chemin complet du fichier de données dont les données doivent être copiées dans la table cible.
SELECT * FROM OPENROWSET(
BULK 'C:\DATA\inv-2017-01-19.csv',
SINGLE_CLOB
) AS DATA;
À compter de SQL Server 2017 (14.x), le data_file peut se trouver dans le Stockage Blob Azure. Pour obtenir des exemples, consultez Exemples d’accès en bloc aux données dans Stockage Blob Azure.
Important
Azure SQL Database prend uniquement en charge la lecture à partir du Stockage Blob Azure.
Options de gestion des erreurs BULK
ERRORFILE = 'file_name'
Fichier utilisé pour collecter les lignes comportant des erreurs de mise en forme et ne pouvant pas être converties en un ensemble de lignes OLE DB. Ces lignes sont copiées « en l'état » du fichier de données vers ce fichier d'erreur.
Le fichier d'erreur est créé au début de l'exécution de la commande. Une erreur est générée si le fichier existe déjà. De plus, un fichier de contrôle portant l'extension .ERROR.txt est créé. Il fait référence à chacune des lignes du fichier d’erreur et propose un diagnostic. Une fois les erreurs corrigées, les données peuvent être chargées.
À partir de SQL Server 2017 (14.x), error_file_path
peut se trouver dans Stockage Blob Azure.
ERRORFILE_DATA_SOURCE_NAME
À compter de SQL Server 2017 (14.x), cet argument est une source de données externe nommée pointant vers l’emplacement de stockage Blob Azure du fichier d’erreur qui contiendra des erreurs trouvées pendant l’importation. La source de données externe doit être créée à l’aide de TYPE = BLOB_STORAGE
. Pour plus d’informations, consultez CRÉER UNE SOURCE DE DONNÉES EXTERNES.
MAXERRORS = maximum_errors
Spécifie le nombre maximal d’erreurs de syntaxe ou de lignes non conformes, tel que défini dans le fichier de format, qui peut se produire avant OPENROWSET
de lever une exception. Jusqu’à ce qu’elle MAXERRORS
soit atteinte, OPENROWSET
ignore chaque ligne incorrecte, ne la charge pas et compte la ligne incorrecte comme une erreur.
La valeur par défaut de maximum_errors est 10.
Remarque
MAX_ERRORS
ne s’applique pas aux CHECK
contraintes ou à la conversion des types de données bigint et d’argent.
Options de traitement des données BULK
FIRSTROW = first_row
Numéro de la première ligne à charger. La valeur par défaut est 1. Cela indique la première ligne du fichier de données spécifié. Les numéros des lignes sont déterminés en comptant les indicateurs de fin de ligne. FIRSTROW
est basé sur 1.
LASTROW = last_row
Numéro de la dernière ligne à charger. La valeur par défaut est 0. Cela indique la dernière ligne du fichier de données spécifié.
ROWS_PER_BATCH = rows_per_batch
Spécifie le nombre approximatif de lignes de données que compte le fichier de données. Cette valeur doit être du même ordre que le nombre réel de lignes.
OPENROWSET
importe toujours un fichier de données en un seul lot. Toutefois, si vous spécifiez une valeur > 0 pour rows_per_batch, le processeur de requêtes se base sur la valeur de rows_per_batch pour allouer les ressources dans le plan de requête.
Par défaut, ROWS_PER_BATCH
est inconnu. La spécification ROWS_PER_BATCH = 0
est la même que l’omission ROWS_PER_BATCH
.
ORDER ( { column [ ASC | DESC ] } [ ,... n ] [ UNIQUE ] )
Indicateur facultatif qui spécifie la manière dont sont triées les données dans le fichier de données. Par défaut, le processus de chargement en masse considère que le fichier de données n'est pas trié. Les performances peuvent s’améliorer si l’optimiseur de requête peut exploiter l’ordre pour générer un plan de requête plus efficace. La liste suivante fournit des exemples pour spécifier un tri peut être bénéfique :
- Insertion de lignes dans une table qui a un index cluster, où les données d'un ensemble de lignes sont triées sur la clé d'index cluster.
- Jointure de l'ensemble de lignes avec une autre table, où les colonnes de tri et de jointure correspondent.
- Agrégation des données de l'ensemble de lignes en fonction des colonnes de tri.
- Utilisation de l’ensemble de lignes comme table source dans la
FROM
clause d’une requête, où correspondent les colonnes de tri et de jointure.
UNIQUE
Spécifie que le fichier de données n’a pas d’entrées en double.
Si les lignes réelles du fichier de données ne sont pas triées selon l’ordre spécifié, ou si l’indicateur UNIQUE
est spécifié et que les clés dupliquées sont présentes, une erreur est retournée.
Les alias de colonne sont requis quand ORDER
ils sont utilisés. La liste d’alias de colonne doit référencer la table dérivée accessible par la BULK
clause. Les noms de colonnes spécifiés dans la ORDER
clause font référence à cette liste d’alias de colonne. Les types de valeurs volumineuses (varchar(max), nvarchar(max), varbinary(max)et xml) et les types DOB (texte, ntext et image) ne peuvent pas être spécifiés.
SINGLE_BLOB
Retourne le contenu de data_file sous la forme d’un ensemble de lignes à une seule ligne et une seule colonne de type varbinary(max) .
Important
Nous vous recommandons d’importer des données XML uniquement à l’aide de l’option SINGLE_BLOB
, plutôt que SINGLE_CLOB
de et SINGLE_NCLOB
, car seule SINGLE_BLOB
prend en charge toutes les conversions d’encodage Windows.
SINGLE_CLOB
La lecture de data_file au format ASCII retourne son contenu sous la forme d’un ensemble de lignes à une seule ligne et une seule colonne du type varchar(max) en utilisant le classement de la base de données active.
SINGLE_NCLOB
En lisant data_file en tant qu’Unicode, retourne le contenu sous la forme d’un ensemble de lignes à une seule ligne de type nvarchar(max), à l’aide du classement de la base de données active.
SELECT * FROM OPENROWSET(
BULK N'C:\Text1.txt',
SINGLE_NCLOB
) AS Document;
Options de format de fichier d’entrée BULK
CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }
Indique la page de codes des données dans le fichier. CODEPAGE
est pertinent uniquement si les données contiennent des colonnes char, varchar ou texte avec des valeurs de caractères supérieures à 127 ou inférieures à 32.
Important
CODEPAGE
n’est pas une option prise en charge sur Linux.
Remarque
Nous vous recommandons de spécifier un nom de classement pour chaque colonne dans un fichier de format, sauf lorsque vous souhaitez que l’option 65001 soit prioritaire sur la spécification de page de codes/classement.
Valeur CODEPAGE | Description |
---|---|
ACP |
Convertit les colonnes de type de données char, varchar ou text de la page de codes ANSI/Microsoft Windows (ISO 1252) à la page de codes SQL Server. |
OEM (valeur par défaut) |
Convertit les colonnes de type de données char, varchar ou text de la page de codes du système OEM à la page de codes SQL Server. |
RAW |
Aucune conversion n'a lieu d'une page de codes à une autre. Il s'agit de l'option la plus rapide. |
code_page |
Indique la page de codes source sur laquelle est basé l'encodage des données caractères du fichier de données, par exemple 850. Les versions importantes avant SQL Server 2016 (13.x) ne prennent pas en charge la page de codes 65001 (encodage UTF-8). |
FORMAT = { 'CSV' | 'PARQUET' | 'DELTA' }
À compter de SQL Server 2017 (14.x), cet argument spécifie un fichier de valeurs séparées par des virgules conforme à la norme RFC 4180 .
À compter de SQL Server 2022 (16.x), les formats Parquet et Delta sont pris en charge.
SELECT *
FROM OPENROWSET(BULK N'D:\XChange\test-csv.csv',
FORMATFILE = N'D:\XChange\test-csv.fmt',
FIRSTROW=2,
FORMAT='CSV') AS cars;
FORMATFILE = 'chemin_fichier_format'
Spécifie le chemin complet au fichier de format. SQL Server prend en charge deux types de fichiers de format : XML et non-XML.
Un fichier de format est requis pour définir les types des colonnes dans le jeu de résultats, La seule exception est lorsque SINGLE_CLOB
, SINGLE_BLOB
ou SINGLE_NCLOB
est spécifié ; dans ce cas, le fichier de format n’est pas obligatoire.
Pour plus d’informations sur les fichiers de format, consultez Utiliser un fichier de format pour importer en bloc des données (SQL Server).
À compter de SQL Server 2017 (14.x), format_file_path peut se trouver dans Stockage Blob Azure. Pour obtenir des exemples, consultez Exemples d’accès en bloc aux données dans Stockage Blob Azure.
FIELDQUOTE = 'field_quote'
À compter de SQL Server 2017 (14.x), cet argument spécifie un caractère utilisé comme caractère de guillemet dans le fichier CSV. S’il n’est pas spécifié, le caractère de guillemet ("
) est utilisé comme caractère de guillemet tel que défini dans la norme RFC 4180 .
Notes
OPENROWSET
ne peut être utilisé pour accéder à des données distantes à partir de sources de données OLE DB uniquement si l’option de Registre DisallowAdhocAccess est explicitement définie sur 0 pour le fournisseur spécifié et que l’option de configuration avancée Ad Hoc Distributed Queries est activée. Lorsque ces options ne sont pas définies, le comportement par défaut n’autorise pas l’accès ad hoc.
Lorsque vous accédez à des sources de données OLE DB distantes, l’identité de connexion des connexions approuvées n’est pas automatiquement déléguée à partir du serveur sur lequel le client est connecté au serveur interrogé. Il est nécessaire de configurer la délégation de l'authentification.
Les noms de catalogues et de schémas sont requis si le fournisseur OLE DB prend en charge plusieurs catalogues et schémas dans la source de données spécifiée. Les valeurs du catalogue et du schéma peuvent être omises lorsque le fournisseur OLE DB ne les prend pas en charge. Si le fournisseur prend en charge uniquement les noms de schémas, il est nécessaire de spécifier un nom en deux parties, sous la forme schéma.objet. Si le fournisseur prend en charge uniquement les noms de catalogues, il est nécessaire de spécifier un nom en trois parties, sous la forme catalogue.schéma.objet. Vous devez spécifier des noms en trois parties pour les requêtes directes qui utilisent le fournisseur SQL Server Native Client OLE DB. Pour plus d’informations, consultez Conventions de la syntaxe Transact-SQL.
OPENROWSET
n’accepte pas les variables pour ses arguments.
Tout appel à OPENDATASOURCE
, OPENQUERY
ou OPENROWSET
dans la clause FROM
est évalué séparément et indépendamment de tout appel à ces fonctions utilisé comme cible de la mise à jour, même si des arguments identiques sont fournis aux deux appels. En particulier, les conditions de filtre ou de jointure appliquées au résultat de l’un de ces appels n’ont aucun effet sur les résultats de l’autre.
Utiliser OPENROWSET avec l’option BULK
Les améliorations Transact-SQL suivantes prennent en charge la OPENROWSET(BULK...)
fonction :
Une clause
FROM
utilisée avecSELECT
peut appelerOPENROWSET(BULK...)
au lieu d’un nom de table, avec les fonctionnalités complètes deSELECT
.OPENROWSET
utilisée avec l’optionBULK
nécessite un nom de corrélation, également baptisé variable de plage ou alias, dans la clauseFROM
. Vous pouvez définir des alias de colonnes. Si aucune liste d’alias de colonne n’est spécifiée, le fichier de format doit avoir des noms de colonnes. La spécification des alias de colonnes remplace les noms de colonnes dans le fichier de format, par exemple :FROM OPENROWSET(BULK...) AS table_alias
FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
Important
Un échec d’ajout de
AS <table_alias>
génère l’erreur : Msg 491, Niveau 16, État 1, Ligne 20 Un nom de corrélation doit être spécifié pour l’ensemble de lignes en bloc dans la clause FROM.Une instruction
SELECT...FROM OPENROWSET(BULK...)
interroge directement les données d’un fichier, sans les importer dans une table. Les instructionsSELECT...FROM OPENROWSET(BULK...)
peuvent également énumérer les alias de colonnes en bloc en utilisant un fichier de format pour spécifier les noms de colonnes ainsi que les types de données.L’utilisation de
OPENROWSET(BULK...)
en tant que table source dans une instructionINSERT
ouMERGE
importe en bloc les données d’un fichier de données dans une table SQL Server. Pour plus d’informations, consultez Utiliser BULK INSERT ou OPENROWSET(BULK...) pour importer des données dans SQL Server.Lorsque l’option
OPENROWSET BULK
est utilisée avec uneINSERT
instruction, laBULK
clause prend en charge les indicateurs de table. En plus des indicateurs de table standard, commeTABLOCK
, la clauseBULK
peut accepter les indicateurs de table spécialisés suivants :IGNORE_CONSTRAINTS
(ignore uniquement les contraintesCHECK
etFOREIGN KEY
),IGNORE_TRIGGERS
,KEEPDEFAULTS
etKEEPIDENTITY
. Pour plus d’informations, consultez Indicateurs de table (Transact-SQL).Pour plus d’informations sur la manière d’utiliser des instructions
INSERT...SELECT * FROM OPENROWSET(BULK...)
, consultez Importation et exportation en bloc de données (SQL Server). Pour savoir à quel moment les opérations d’insertion de ligne effectuées par l’importation en bloc sont consignées dans le journal des transactions, consultez Conditions requises pour une journalisation minimale dans l’importation en bloc.
Remarque
Lorsque vous utilisez OPENROWSET
, il est important de comprendre comment SQL Server gère l’emprunt d’identité. Pour plus d’informations sur les considérations de sécurité, consultez Utiliser BULK INSERT ou OPENROWSET(BULK...) pour importer des données dans SQL Server.
Importation en bloc de données SQLCHAR, SQLNCHAR ou SQLBINARY
OPENROWSET(BULK...)
suppose que, s’il n’est pas spécifié, la longueur maximale de SQLCHAR
, SQLNCHAR
ou SQLBINARY
les données ne dépassent pas 8 000 octets. Si les données importées se situent dans un champ de données métier qui contient des objets varchar(max), nvarchar(max)ou varbinary(max) qui dépassent 8 000 octets, vous devez utiliser un fichier de format XML qui définit la longueur maximale du champ de données. Pour spécifier la longueur maximale, modifiez le fichier de format et déclarez l'attribut MAX_LENGTH.
Remarque
Un fichier de format généré automatiquement ne spécifie pas la longueur ou la longueur maximale d’un champ métier. Toutefois, vous pouvez modifier un fichier de format et spécifier manuellement la longueur ou la longueur maximale.
Exportation et importation en bloc de documents SQLXML
Pour exporter ou importer en bloc des données SQLXML, utilisez l'un des types de données ci-dessous dans votre fichier de format.
Type de données | Résultat |
---|---|
SQLCHAR ou SQLVARYCHAR |
Les données sont envoyées dans la page de codes client ou dans la page de codes implicite par le classement. |
SQLNCHAR ou SQLNVARCHAR |
Les données sont envoyées au format Unicode. |
SQLBINARY ou SQLVARYBIN |
Les données sont envoyées sans être converties. |
Autorisations
Les autorisations OPENROWSET
sont conditionnées par les autorisations associées au nom d’utilisateur passé au fournisseur OLE DB. L’utilisation de l’option BULK
nécessite l’autorisation ADMINISTER BULK OPERATIONS
ou ADMINISTER DATABASE BULK OPERATIONS
.
Exemples
Cette section fournit des exemples généraux pour montrer comment utiliser OPENROWSET.
A. Utiliser OPENROWSET avec SELECT et le fournisseur OLE DB SQL Server Native Client
S’applique uniquement à : SQL Server.
SQL Server Native Client (souvent abrégé en SNAC) a été supprimé dans SQL Server 2022 (16.x) et SQL Server Management Studio 19 (SSMS). Le fournisseur OLE DB pour SQL Server Native Client (SQLNCLI ou SQLNCLI11) et le fournisseur Microsoft OLE DB hérité pour SQL Server (SQLOLEDB) ne sont pas recommandés dans les nouveaux développements. Utilisez à la place le nouveau Microsoft OLE DB Driver (MSOLEDBSQL) pour SQL Server.
L’exemple suivant utilise le fournisseur SQL Server Native Client OLE DB pour accéder à la table HumanResources.Department
de la base de données AdventureWorks2022
sur le serveur distant Seattle1
. (L'utilisation de SQLNCLI et SQL Server vous redirigera vers la version la plus récente du fournisseur SQL Server Native Client OLE DB.) Une instruction SELECT
définit l’ensemble de lignes retourné. La chaîne de caractères du fournisseur contient les mots clés Server
et Trusted_Connection
. Ces mots clés sont reconnus par le fournisseur SQL Server Native Client OLE DB.
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. Utiliser le fournisseur Microsoft OLE DB pour Jet
S’applique uniquement à : SQL Server.
L'exemple suivant accède à la table Customers
de la base de données Microsoft Access Northwind
via le fournisseur Microsoft OLE DB pour Jet.
Remarque
Cet exemple suppose que Microsoft Access est installé. Pour exécuter cet exemple, vous devez installer la Northwind
base de données.
SELECT CustomerID, CompanyName
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',
Customers
);
Important
Azure SQL Database prend uniquement en charge la lecture à partir du Stockage Blob Azure.
C. Utiliser OPENROWSET et une autre table dans une jointure INTERNE
S’applique uniquement à : SQL Server.
L’exemple de code suivant sélectionne toutes les données de la table Customers
dans la base de données Northwind
installée sur l’instance locale de SQL Server et dans la table Orders
de la base de données Northwind
Access stockée sur le même ordinateur.
Notes
L'exécution de ce code exemple suppose que Microsoft Access est installé. Pour exécuter cet exemple, vous devez installer la Northwind
base de données.
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;
Important
Azure SQL Database prend uniquement en charge la lecture à partir du stockage Blob Azure.
D. Utiliser OPENROWSET pour les données de fichier BULK INSERT dans une colonne varbinary(max)
S’applique uniquement à : SQL Server.
L’exemple suivant crée une petite table à des fins de démonstration et insère des données de fichier à partir d’un fichier nommé Text1.txt
dans le C:
répertoire racine dans une colonne 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
Important
Azure SQL Database prend uniquement en charge la lecture à partir du stockage Blob Azure.
E. Utiliser le fournisseur OPENROWSET BULK avec un fichier de format pour récupérer des lignes à partir d’un fichier texte
S’applique uniquement à : SQL Server.
Le code exemple suivant utilise un fichier de format pour extraire des lignes d'un fichier texte dont les données sont délimitées par des tabulations, values.txt
, qui contient les données suivantes :
1 Data Item 1
2 Data Item 2
3 Data Item 3
Le fichier de format, values.fmt
, décrit les colonnes du fichier 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
Cette requête récupère ces données :
SELECT a.* FROM OPENROWSET(
BULK 'C:\test\values.txt',
FORMATFILE = 'C:\test\values.fmt'
) AS a;
Important
Azure SQL Database prend uniquement en charge la lecture à partir du Stockage Blob Azure.
F. Spécifier un fichier de format et une page de codes
S’applique uniquement à : SQL Server.
L’exemple suivant montre comment utiliser les options de fichier de format et de page de codes en même temps.
INSERT INTO MyTable
SELECT a.* FROM OPENROWSET (
BULK N'D:\data.csv',
FORMATFILE = 'D:\format_no_collation.txt',
CODEPAGE = '65001'
) AS a;
G. Accéder aux données à partir d’un fichier CSV avec un fichier de format
S’applique à : SQL Server 2017 (14.x) et versions ultérieures uniquement.
SELECT * FROM OPENROWSET(
BULK N'D:\XChange\test-csv.csv',
FORMATFILE = N'D:\XChange\test-csv.fmt',
FIRSTROW = 2,
FORMAT = 'CSV'
) AS cars;
Important
Azure SQL Database prend uniquement en charge la lecture à partir du Stockage Blob Azure.
H. Accéder aux données à partir d’un fichier CSV sans fichier de format
S’applique uniquement à : 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'
);
Important
Le pilote ODBC doit être 64 bits. Ouvrez l’onglet Pilotes de l’application Se connecter à une source de données ODBC (Assistant Importation et Exportation SQL Server) dans Windows pour vérifier cela. Il existe 32 bits Microsoft Text Driver (*.txt, *.csv)
qui ne fonctionneront pas avec une version 64 bits de sqlservr.exe
.
I. Accéder aux données à partir d’un fichier stocké sur Stockage Blob Azure
S’applique à : SQL Server 2017 (14.x) et versions ultérieures uniquement.
Dans SQL Server 2017 (14.x) et versions ultérieures, l’exemple suivant utilise une source de données externe qui pointe vers un conteneur dans un compte de stockage Azure et des informations d’identification délimitées à la base de données créées pour une signature d’accès partagé.
SELECT * FROM OPENROWSET(
BULK 'inv-2017-01-19.csv',
DATA_SOURCE = 'MyAzureInvoices',
SINGLE_CLOB
) AS DataFile;
Pour obtenir des exemples completsOPENROWSET
, notamment la configuration des informations d’identification et de la source de données externe, consultez Exemples d’accès en bloc aux données dans Stockage Blob Azure.
J. Importer dans une table à partir d’un fichier stocké sur Stockage Blob Azure
L’exemple suivant montre comment utiliser la OPENROWSET
commande pour charger des données à partir d’un fichier csv dans un emplacement de stockage Blob Azure sur lequel vous avez créé la clé SAP. L’emplacement du stockage Blob Azure est configuré comme source de données externe. Ceci nécessite des informations d’identification délimitées à la base de données avec une signature d’accès partagé chiffrée à l’aide d’une clé principale dans la base de données utilisateur.
-- 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;
Important
Azure SQL Database prend uniquement en charge la lecture à partir du Stockage Blob Azure.
K. Utiliser une identité managée pour une source externe
L’exemple suivant crée des informations d’identification en utilisant une identité managée, crée une source externe, puis charge des données à partir d’un fichier CSV hébergé sur la source externe.
Commencez par créer les informations d’identification et spécifier un stockage d’objets blob comme source externe :
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
);
Ensuite, chargez les données du fichier CSV hébergé sur le stockage d’objets blob :
SELECT * FROM OPENROWSET(
BULK 'Test - Copy.csv',
DATA_SOURCE = 'SampleSource',
SINGLE_CLOB
) as test;
Important
Azure SQL Database prend uniquement en charge la lecture à partir du Stockage Blob Azure.
L. Utiliser OPENROWSET pour accéder à plusieurs fichiers Parquet à l’aide du stockage d’objets compatible avec S3
S’applique à : SQL Server 2022 (16.x) et versions ultérieures.
L’exemple suivant utilise plusieurs fichiers Parquet à partir d’un emplacement différent, tous stockés sur le stockage d’objets compatible 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. Utiliser OPENROWSET pour accéder à plusieurs fichiers Delta à partir d’Azure Data Lake Gen2
S’applique à : SQL Server 2022 (16.x) et versions ultérieures.
Dans cet exemple, le conteneur de table de données est nommé Contoso
et se trouve sur un compte de stockage 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;
Autres exemples
Pour plus d’exemples qui montrent l’utilisation INSERT...SELECT * FROM OPENROWSET(BULK...)
, consultez les articles suivants :
- Exemples d'importation et d'exportation en bloc de documents XML (SQL Server)
- Conserver des valeurs d'identité lors de l'importation de données en bloc (SQL Server)
- Conserver les valeurs null ou les valeurs par défaut lors de l’importation en bloc (SQL Server)
- Utiliser un fichier de format pour importer des données en bloc (SQL Server)
- Utiliser le format caractère pour importer ou exporter des données (SQL Server)
- Utiliser un fichier de format pour ignorer une colonne de table (SQL Server)
- Utiliser un fichier de format pour ignorer un champ de données (SQL Server)
- Utiliser un fichier de format pour mapper les colonnes d’une table aux champs d’un fichier de données (SQL Server)
- Interroger des sources de données à l’aide d’OPENROWSET dans Azure SQL Managed Instances
Contenu connexe
- DELETE (Transact-SQL)
- Clause FROM plus JOIN, APPLY, PIVOT (Transact-SQL)
- Importation et exportation en bloc de données (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)
Commentaires
https://aka.ms/ContentUserFeedback.
Bientôt disponible : Tout au long de l’année 2024, nous abandonnerons progressivement le mécanisme de retour d’information GitHub Issues pour le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez :Soumettre et afficher des commentaires pour