OPENROWSET (Transact-SQL)

Artikel
01/02/2025

van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance

Bevat alle verbindingsgegevens die vereist zijn voor toegang tot externe gegevens vanuit een OLE DB-gegevensbron. Deze methode is een alternatief voor het openen van tabellen op een gekoppelde server en is een eenmalige ad-hocmethode voor het verbinden en openen van externe gegevens met behulp van OLE DB. Gebruik in plaats daarvan gekoppelde servers voor frequentere verwijzingen naar OLE DB-gegevensbronnen. Zie Gekoppelde servers (Database Engine)voor meer informatie. De functie OPENROWSET kan worden verwezen in de FROM component van een query alsof deze een tabelnaam is. De functie OPENROWSET kan ook worden verwezen als de doeltabel van een INSERT, UPDATEof DELETE-instructie, afhankelijk van de mogelijkheden van de OLE DB-provider. Hoewel de query meerdere resultatensets kan retourneren, retourneert OPENROWSET slechts de eerste.

OPENROWSET biedt ook ondersteuning voor bulkbewerkingen via een ingebouwde BULK provider waarmee gegevens uit een bestand kunnen worden gelezen en geretourneerd als een rijenset.

Veel voorbeelden in dit artikel zijn alleen van toepassing op SQL Server. Details en koppelingen naar vergelijkbare voorbeelden op andere platforms:

Azure SQL Database biedt alleen ondersteuning voor lezen vanuit Azure Blob Storage.
Zie Query's uitvoeren op gegevensbronnen met behulp van OPENROWSETvoor voorbeelden van Azure SQL Managed Instance.
Zie OpenROWSET gebruiken met behulp van een serverloze SQL-pool in Azure Synapse Analyticsvoor informatie en voorbeelden van serverloze SQL-pools in Azure Synapse Analytics.
Toegewezen SQL-pools in Azure Synapse bieden geen ondersteuning voor de OPENROWSET-functie.

Transact-SQL syntaxisconventies

Syntaxis

OPENROWSET syntaxis wordt gebruikt om een query uit te voeren op externe gegevensbronnen:

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

OPENROWSET(BULK) syntaxis wordt gebruikt om externe bestanden te lezen:

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

Argumenten

Algemene argumenten

'provider_name'

Een tekenreeks die de beschrijvende naam (of PROGID) van de OLE DB-provider vertegenwoordigt, zoals opgegeven in het register. provider_name heeft geen standaardwaarde. Voorbeelden van providernamen zijn Microsoft.Jet.OLEDB.4.0, SQLNCLIof MSDASQL.

'gegevensbron'

Een tekenreeksconstante die overeenkomt met een bepaalde OLE DB-gegevensbron. gegevensbron is de DBPROP_INIT_DATASOURCE eigenschap die moet worden doorgegeven aan de IDBProperties-interface van de provider om de provider te initialiseren. Deze tekenreeks bevat doorgaans de naam van het databasebestand, de naam van een databaseserver of een naam die de provider begrijpt voor het zoeken naar de database of databases.

De gegevensbron kan een bestandspad zijn C:\SAMPLES\Northwind.mdb' voor Microsoft.Jet.OLEDB.4.0 provider of verbindingsreeks Server=Seattle1;Trusted_Connection=yes; voor SQLNCLI provider.

'user_id'

Een tekenreeksconstante die de gebruikersnaam is die wordt doorgegeven aan de opgegeven OLE DB-provider. user_id geeft de beveiligingscontext voor de verbinding op en wordt doorgegeven als de eigenschap DBPROP_AUTH_USERID om de provider te initialiseren. user_id kan geen microsoft Windows-aanmeldingsnaam zijn.

'wachtwoord'

Een tekenreeksconstante die het gebruikerswachtwoord is dat moet worden doorgegeven aan de OLE DB-provider. wachtwoord wordt doorgegeven als de eigenschap DBPROP_AUTH_PASSWORD bij het initialiseren van de provider. wachtwoord kan geen Microsoft Windows-wachtwoord zijn.

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

'provider_string'

Een providerspecifieke verbindingsreeks die wordt doorgegeven als de eigenschap DBPROP_INIT_PROVIDERSTRING om de OLE DB-provider te initialiseren. provider_string bevat doorgaans alle verbindingsgegevens die nodig zijn om de provider te initialiseren. Zie voor een lijst met trefwoorden die de OLE DB-provider van sql Server Native Client herkent initialisatie- en autorisatie-eigenschappen (Native Client OLE DB Provider).

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

<table_or_view>

Externe tabel of weergave met de gegevens die OPENROWSET moeten lezen. Het kan een driedelige naamobject zijn met de volgende onderdelen:

catalogus (optioneel): de naam van de catalogus of database waarin het opgegeven object zich bevindt.
schema (optioneel): de naam van het schema of de objecteigenaar voor het opgegeven object.
object: de objectnaam waarmee het object uniek wordt geïdentificeerd.

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

'query'

Een tekenreeksconstante die door de provider wordt verzonden en uitgevoerd. De lokale instantie van SQL Server verwerkt deze query niet, maar verwerkt queryresultaten die door de provider worden geretourneerd, een passthrough-query. Passthrough-query's zijn handig wanneer ze worden gebruikt voor providers die hun tabelgegevens niet beschikbaar maken via tabelnamen, maar alleen via een opdrachttaal. Passthrough-query's worden ondersteund op de externe server, zolang de queryprovider het OLE DB-opdrachtobject en de verplichte interfaces ondersteunt. Zie OLE DB-interfaces (SQL Server Native Client)voor meer informatie.

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

BULKargumenten

Gebruikt de BULK rijensetprovider voor OPENROWSET om gegevens uit een bestand te lezen. In SQL Server kunt OPENROWSET lezen uit een gegevensbestand zonder de gegevens in een doeltabel te laden. Hiermee kunt u OPENROWSET gebruiken met een eenvoudige SELECT instructie.

Belangrijk

Azure SQL Database biedt alleen ondersteuning voor lezen vanuit Azure Blob Storage.

Met de argumenten van de optie BULK kunt u bepalen waar gegevens moeten worden gestart en beëindigd, hoe fouten moeten worden verwerkt en hoe gegevens worden geïnterpreteerd. U kunt bijvoorbeeld opgeven dat het gegevensbestand wordt gelezen als een rij met één rij, een rijset met één kolom van het type varbinaire, varcharof nvarchar. Het standaardgedrag wordt beschreven in de argumentbeschrijvingen die volgen.

Zie de sectie Opmerkingen verderop in dit artikel voor meer informatie over het gebruik van de optie BULK. Zie de sectie Machtigingen verderop in dit artikel voor meer informatie over de machtigingen die de BULK optie vereist.

Notitie

Wanneer u gegevens importeert met het volledige herstelmodel, optimaliseert OPENROWSET (BULK ...) logboekregistratie niet.

Zie Gegevens voorbereiden voor bulkexport of importvoor informatie over het voorbereiden van gegevens voor bulkimport.

BULK 'data_file'

Het volledige pad van het gegevensbestand waarvan de gegevens moeten worden gekopieerd naar de doeltabel.

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

Vanaf SQL Server 2017 (14.x) kan de data_file zich in Azure Blob Storage bevinden. Zie Voorbeelden van bulktoegang tot gegevens in Azure Blob Storagevoor voorbeelden.

Belangrijk

Azure SQL Database biedt alleen ondersteuning voor lezen vanuit Azure Blob Storage.

Opties voor BULK-foutafhandeling

ERRORFILE = 'file_name'

Hiermee geeft u het bestand op dat wordt gebruikt voor het verzamelen van rijen met opmaakfouten en die niet kunnen worden geconverteerd naar een OLE DB-rijenset. Deze rijen worden naar dit foutbestand gekopieerd uit het gegevensbestand 'zoals is'.

Het foutbestand wordt gemaakt aan het begin van de opdrachtuitvoering. Er treedt een fout op als het bestand al bestaat. Daarnaast wordt een besturingselementbestand met de extensie .ERROR.txt gemaakt. Dit bestand verwijst naar elke rij in het foutenbestand en biedt foutdiagnose. Nadat de fouten zijn gecorrigeerd, kunnen de gegevens worden geladen.

Vanaf SQL Server 2017 (14.x) kan de error_file_path zich in Azure Blob Storage bevinden.

ERRORFILE_DATA_SOURCE_NAME

Vanaf SQL Server 2017 (14.x) is dit argument een benoemde externe gegevensbron die verwijst naar de Azure Blob-opslaglocatie van het foutenbestand dat fouten bevat die zijn gevonden tijdens het importeren. De externe gegevensbron moet worden gemaakt met behulp van de TYPE = BLOB_STORAGE. Zie CREATE EXTERNAL DATA SOURCEvoor meer informatie.

MAXERRORS = maximum_errors

Hiermee geeft u het maximum aantal syntaxisfouten of niet-conforme rijen op, zoals gedefinieerd in het indelingsbestand, dat kan optreden voordat OPENROWSET een uitzondering genereert. Totdat MAXERRORS is bereikt, negeert OPENROWSET elke ongeldige rij, laadt deze niet en telt de slechte rij als één fout.

De standaardwaarde voor maximum_errors is 10.

Notitie

MAX_ERRORS is niet van toepassing op CHECK beperkingen of op het converteren van geld en bigint gegevenstypen.

Opties voor BULKgegevensverwerking

FIRSTROW = first_row

Hiermee geeft u het nummer op van de eerste rij die moet worden geladen. De standaardwaarde is 1. Dit geeft de eerste rij in het opgegeven gegevensbestand aan. De rijnummers worden bepaald door de rijeindtekens te tellen. FIRSTROW is gebaseerd op 1.

LASTROW = last_row

Hiermee geeft u het nummer op van de laatste rij die moet worden geladen. De standaardwaarde is 0. Dit geeft de laatste rij in het opgegeven gegevensbestand aan.

ROWS_PER_BATCH = rows_per_batch

Hiermee geeft u het geschatte aantal rijen met gegevens in het gegevensbestand. Deze waarde moet dezelfde volgorde hebben als het werkelijke aantal rijen.

OPENROWSET importeert altijd een gegevensbestand als één batch. Als u echter rows_per_batch opgeeft met een waarde > 0, gebruikt de queryprocessor de waarde van rows_per_batch als hint voor het toewijzen van resources in het queryplan.

Standaard is ROWS_PER_BATCH onbekend. Het opgeven van ROWS_PER_BATCH = 0 is hetzelfde als het weglaten van ROWS_PER_BATCH.

ORDER ( { kolom [ ASC | DESC ] } [ ,... n ] [ UNIEK ] )

Een optionele hint die aangeeft hoe de gegevens in het gegevensbestand worden gesorteerd. Standaard wordt bij de bulkbewerking ervan uitgegaan dat het gegevensbestand niet is gerangschikt. De prestaties kunnen worden verbeterd als de queryoptimalisatie de volgorde kan benutten om een efficiënter queryplan te genereren. De volgende lijst bevat voorbeelden voor wanneer u een sorteerbewerking opgeeft, kan nuttig zijn:

Rijen invoegen in een tabel met een geclusterde index, waarbij de rijensetgegevens worden gesorteerd op de geclusterde indexsleutel.
De rijenset samenvoegen met een andere tabel, waarbij de kolommen sorteren en samenvoegen overeenkomen.
De rijensetgegevens samenvoegen op basis van de sorteerkolommen.
Gebruik de rijenset als een brontabel in de FROM component van een query, waarbij de kolommen sorteren en samenvoegen overeenkomen.

UNIEK

Hiermee geeft u op dat het gegevensbestand geen dubbele vermeldingen bevat.

Als de werkelijke rijen in het gegevensbestand niet worden gesorteerd op basis van de opgegeven volgorde of als de UNIQUE hint is opgegeven en er dubbele sleutels aanwezig zijn, wordt een fout geretourneerd.

Kolomaliassen zijn vereist wanneer ORDER wordt gebruikt. De lijst met kolomaliassen moet verwijzen naar de afgeleide tabel die wordt geopend door de BULK-component. De kolomnamen die zijn opgegeven in de ORDER component verwijzen naar deze kolomaliaslijst. Grote waardetypen (varchar(max), nvarchar(max), varbinary(max)en XML-) en lob-typen (grote objecten) (tekst, ntexten afbeelding) kunnen niet worden opgegeven.

SINGLE_BLOB

Retourneert de inhoud van data_file als een rijset met één rij met één kolom van het type varbinary(max).

Belangrijk

U wordt aangeraden alleen XML-gegevens te importeren met behulp van de optie SINGLE_BLOB, in plaats van SINGLE_CLOB en SINGLE_NCLOB, omdat alleen SINGLE_BLOB alle Windows-coderingsconversies ondersteunt.

SINGLE_CLOB

Als u data_file als ASCII leest, wordt de inhoud geretourneerd als een rijset met één rij met één rij van het type varchar(max), met behulp van de sortering van de huidige database.

SINGLE_NCLOB

Als u data_file als Unicode leest, wordt de inhoud geretourneerd als een rijset met één rij met één rij van het type nvarchar(max), met behulp van de sortering van de huidige database.

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

Opties voor BULK-invoerbestandsindeling

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

Hiermee geeft u de codepagina van de gegevens in het gegevensbestand. CODEPAGE is alleen relevant als de gegevens teken, varcharof tekst kolommen met tekenwaarden meer dan 127 of minder dan 32 bevatten.

Belangrijk

CODEPAGE is geen ondersteunde optie in Linux.

Notitie

U wordt aangeraden een sorteringsnaam op te geven voor elke kolom in een indelingsbestand, behalve wanneer u wilt dat de optie 65001 prioriteit heeft ten opzichte van de specificatie van de sortering/codepagina.

CODEPAGE-waarde	Beschrijving
`ACP`	Converteert kolommen van teken, varcharof tekst gegevenstype van de ANSI-/Microsoft Windows-codepagina (ISO 1252) naar de SQL Server-codepagina.
`OEM` (standaard)	Converteert kolommen van teken, varcharof tekst gegevenstype van de systeem-OEM-codepagina naar de pagina MET SQL Server-code.
`RAW`	Er wordt geen conversie uitgevoerd van de ene codepagina naar de andere. Dit is de snelste optie.
`code_page`	Geeft de broncodepagina aan waarop de tekengegevens in het gegevensbestand zijn gecodeerd; bijvoorbeeld 850. Belangrijke versies vóór SQL Server 2016 (13.x) bieden geen ondersteuning voor codepagina 65001 (UTF-8-codering).

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

Vanaf SQL Server 2017 (14.x) geeft dit argument een bestand met door komma's gescheiden waarden op dat compatibel is met de RFC 4180-standaard.

Vanaf SQL Server 2022 (16.x) worden zowel Parquet- als Delta-indelingen ondersteund.

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'

Hiermee geeft u het volledige pad van een indelingsbestand. SQL Server ondersteunt twee typen indelingsbestanden: XML en niet-XML.

Een indelingsbestand is vereist voor het definiëren van kolomtypen in de resultatenset. De enige uitzondering is wanneer SINGLE_CLOB, SINGLE_BLOBof SINGLE_NCLOB is opgegeven; In dat geval is het indelingsbestand niet vereist.

Zie Een indelingsbestand gebruiken om gegevens (SQL Server) bulksgewijs te importerenvoor meer informatie over het opmaken van bestanden.

Vanaf SQL Server 2017 (14.x) kan de format_file_path zich in Azure Blob Storage bevinden. Zie Voorbeelden van bulktoegang tot gegevens in Azure Blob Storagevoor voorbeelden.

FIELDQUOTE = 'field_quote'

Vanaf SQL Server 2017 (14.x) geeft dit argument een teken op dat wordt gebruikt als het aanhalingsteken in het CSV-bestand. Als dit niet is opgegeven, wordt het aanhalingsteken (") gebruikt als het aanhalingsteken zoals gedefinieerd in de RFC 4180 standaard.

Opmerkingen

OPENROWSET kan alleen worden gebruikt voor toegang tot externe gegevens uit OLE DB-gegevensbronnen wanneer de registeroptie DisallowAdhocAccess expliciet is ingesteld op 0 voor de opgegeven provider en de geavanceerde configuratieoptie Ad Hoc gedistribueerde query's is ingeschakeld. Wanneer deze opties niet zijn ingesteld, is het standaardgedrag niet toegestaan voor ad-hoctoegang.

Wanneer u externe OLE DB-gegevensbronnen opent, wordt de aanmeldings-id van vertrouwde verbindingen niet automatisch gedelegeerd vanaf de server waarop de client is verbonden met de server waarop een query wordt uitgevoerd. Verificatiedelegering moet worden geconfigureerd.

Catalogus- en schemanamen zijn vereist als de OLE DB-provider meerdere catalogi en schema's in de opgegeven gegevensbron ondersteunt. Waarden voor catalogus en schema kunnen worden weggelaten wanneer de OLE DB-provider deze niet ondersteunt. Als de provider alleen schemanamen ondersteunt, wordt een tweedelige naam van het formulier schema.object moet worden opgegeven. Als de provider alleen catalogusnamen ondersteunt, wordt er een driedelige naam van het formulier catalogus.schema.object moet worden opgegeven. Driedelige namen moeten worden opgegeven voor passthrough-query's die gebruikmaken van de OLE DB-provider van de SQL Server-client. Zie Transact-SQL syntaxisconventiesvoor meer informatie.

OPENROWSET accepteert geen variabelen voor de argumenten.

Elke aanroep van OPENDATASOURCE, OPENQUERYof OPENROWSET in de FROM component wordt afzonderlijk en onafhankelijk van elke aanroep naar deze functies die worden gebruikt als doel van de update, zelfs als identieke argumenten aan de twee aanroepen worden verstrekt. Met name filter- of joinvoorwaarden die zijn toegepast op het resultaat van een van deze aanroepen, hebben geen invloed op de resultaten van de andere.

OPENROWSET gebruiken met de optie BULK

De volgende Transact-SQL verbeteringen ondersteunen de functie OPENROWSET(BULK...):

Een FROM-component die wordt gebruikt met SELECT kan OPENROWSET(BULK...) aanroepen in plaats van een tabelnaam, met volledige SELECT functionaliteit.

OPENROWSET met de optie BULK vereist een correlatienaam, ook wel een bereikvariabele of alias genoemd, in de FROM-component. Kolomaliassen kunnen worden opgegeven. Als er geen kolomaliaslijst is opgegeven, moet het indelingsbestand kolomnamen hebben. Als u kolomaliassen opgeeft, worden de kolomnamen in het indelingsbestand overschreven, zoals:
- FROM OPENROWSET(BULK...) AS table_alias
- FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
Belangrijk

Het toevoegen van de AS <table_alias> resulteert in de fout: Msg 491, Niveau 16, State 1, Regel 20 Een correlatienaam moet worden opgegeven voor de bulkrijset in de from-component.
Met een SELECT...FROM OPENROWSET(BULK...)-instructie worden de gegevens in een bestand rechtstreeks opgevraagd zonder de gegevens in een tabel te importeren. SELECT...FROM OPENROWSET(BULK...) instructies kunnen ook bulkkolomaliassen vermelden met behulp van een indelingsbestand om kolomnamen en ook gegevenstypen op te geven.
Als u OPENROWSET(BULK...) als brontabel in een INSERT- of MERGE-instructie gebruikt, importeert u bulksgewijs gegevens uit een gegevensbestand in een SQL Server-tabel. Zie BULK INSERT of OPENROWSET(BULK...) gebruiken om gegevens te importeren in SQL Servervoor meer informatie.
Wanneer de optie OPENROWSET BULK wordt gebruikt met een INSERT-instructie, ondersteunt de BULK-component tabelhints. Naast de normale tabelhints, zoals TABLOCK, kan de BULK-component de volgende gespecialiseerde tabelhints accepteren: IGNORE_CONSTRAINTS (negeert alleen de beperkingen voor CHECK en FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTSen KEEPIDENTITY. Zie tabelhints (Transact-SQL)voor meer informatie.

Zie BulkImport and Export of Data (SQL Server)voor meer informatie over het gebruik van INSERT...SELECT * FROM OPENROWSET(BULK...) instructies. Zie Vereisten voor minimale logboekregistratie in bulkimportvoor informatie over wanneer bewerkingen voor het bulksgewijs importeren van rijen worden uitgevoerd.

Notitie

Wanneer u OPENROWSETgebruikt, is het belangrijk om te begrijpen hoe SQL Server imitatie afhandelt. Zie BULK INSERT of OPENROWSET(BULK...) gebruiken om gegevens te importeren in SQL Servervoor meer informatie over beveiligingsoverwegingen.

SQLCHAR-, SQLNCHAR- of SQLBINARY-gegevens bulksgewijs importeren

OPENROWSET(BULK...) ervan uitgaat dat, indien niet opgegeven, de maximale lengte van SQLCHAR, SQLNCHARof SQLBINARY gegevens niet groter is dan 8000 bytes. Als de geïmporteerde gegevens zich in een LOB-gegevensveld bevinden dat varchar(max), nvarchar(max)of varbinary(max) objecten die groter zijn dan 8000 bytes, moet u een XML-indelingsbestand gebruiken waarmee de maximale lengte voor het gegevensveld wordt gedefinieerd. Als u de maximale lengte wilt opgeven, bewerkt u het indelingsbestand en declareert u het kenmerk MAX_LENGTH.

Notitie

Een automatisch gegenereerd indelingsbestand geeft niet de lengte of maximumlengte voor een LOB-veld op. U kunt echter een indelingsbestand bewerken en handmatig de lengte of maximumlengte opgeven.

SQLXML-documenten bulksgewijs exporteren of importeren

Als u SQLXML-gegevens bulksgewijs wilt exporteren of importeren, gebruikt u een van de volgende gegevenstypen in uw indelingsbestand.

Gegevenstype	Effect
`SQLCHAR` of `SQLVARYCHAR`	De gegevens worden verzonden op de clientcodepagina of op de codepagina die wordt geïmpliceerd door de sortering.
`SQLNCHAR` of `SQLNVARCHAR`	De gegevens worden verzonden als Unicode.
`SQLBINARY` of `SQLVARYBIN`	De gegevens worden zonder conversie verzonden.

Machtigingen

OPENROWSET machtigingen worden bepaald door de machtigingen van de gebruikersnaam die wordt doorgegeven aan de OLE DB-provider. Als u de optie BULK wilt gebruiken, is ADMINISTER BULK OPERATIONS of ADMINISTER DATABASE BULK OPERATIONS machtiging vereist.

Voorbeelden

In deze sectie vindt u algemene voorbeelden om te laten zien hoe u OPENROWSET gebruikt.

Een. OPENROWSET gebruiken met SELECT en de OLE DB-provider van de SQL Server-client

is alleen van toepassing op: SQL Server.

De SQL Server Native Client (vaak afgekort SNAC) is verwijderd uit SQL Server 2022 (16.x) en SQL Server Management Studio 19 (SSMS). Zowel de SQL Server Native Client OLE DB-provider (SQLNCLI of SQLNCLI11) als de verouderde Microsoft OLE DB-provider voor SQL Server (SQLOLEDB) worden niet aanbevolen voor nieuwe ontwikkeling. Schakel over naar het nieuwe Microsoft OLE DB-stuurprogramma (MSOLEDBSQL) voor SQL Server.

In het volgende voorbeeld wordt de OLE DB-provider van sql Server Native Client gebruikt voor toegang tot de HumanResources.Department tabel in de AdventureWorks2022-database op de externe server Seattle1. (Gebruik SQLNCLI en SQL Server worden omgeleid naar de nieuwste versie van SQL Server Native Client OLE DB Provider.) Er wordt een SELECT-instructie gebruikt om de geretourneerde rijset te definiëren. De providertekenreeks bevat de Server en Trusted_Connection trefwoorden. Deze trefwoorden worden herkend door de OLE DB-provider van 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. De Microsoft OLE DB-provider voor Jet gebruiken

is alleen van toepassing op: SQL Server.

In het volgende voorbeeld wordt de Customers tabel in de Microsoft Access Northwind-database geopend via de Microsoft OLE DB-provider voor Jet.

Notitie

In dit voorbeeld wordt ervan uitgegaan dat Microsoft Access is geïnstalleerd. Als u dit voorbeeld wilt uitvoeren, moet u de Northwind-database installeren.

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

Belangrijk

Azure SQL Database biedt alleen ondersteuning voor lezen vanuit Azure Blob Storage.

C. OPENROWSET en een andere tabel in een INNER JOIN gebruiken

is alleen van toepassing op: SQL Server.

In het volgende voorbeeld worden alle gegevens uit de Customers tabel uit het lokale exemplaar van sql Server Northwind-database en uit de Orders tabel uit de Access Northwind-database geselecteerd die op dezelfde computer is opgeslagen.

Notitie

In dit voorbeeld wordt ervan uitgegaan dat Access is geïnstalleerd. Als u dit voorbeeld wilt uitvoeren, moet u de Northwind-database installeren.

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;

Belangrijk

Azure SQL Database biedt alleen ondersteuning voor lezen vanuit Azure Blob Storage.

D. OPENROWSET gebruiken om bestandsgegevens BULKSGEWIJS IN TE VOEGEN in een varbinary(max)-kolom

is alleen van toepassing op: SQL Server.

In het volgende voorbeeld wordt een kleine tabel gemaakt voor demonstratiedoeleinden en worden bestandsgegevens uit een bestand met de naam Text1.txt in de C: hoofdmap ingevoegd in een varbinary(max) kolom.

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

Belangrijk

Azure SQL Database biedt alleen ondersteuning voor lezen vanuit Azure Blob Storage.

E. De OPENROWSET BULK-provider gebruiken met een indelingsbestand om rijen op te halen uit een tekstbestand

is alleen van toepassing op: SQL Server.

In het volgende voorbeeld wordt een indelingsbestand gebruikt om rijen op te halen uit een tekstbestand met tabscheidingstekens, values.txt met de volgende gegevens:

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

Het indelingsbestand, values.fmt, beschrijft de kolommen 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

Met deze query worden die gegevens opgehaald:

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

Belangrijk

Azure SQL Database biedt alleen ondersteuning voor lezen vanuit Azure Blob Storage.

F. Een indelingsbestand en codepagina opgeven

is alleen van toepassing op: SQL Server.

In het volgende voorbeeld ziet u hoe u zowel de indelingsbestands- als codepaginaopties tegelijk gebruikt.

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

G. Toegang tot gegevens uit een CSV-bestand met een indelingsbestand

Van toepassing op: alleen SQL Server 2017 (14.x) en latere versies.

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

Belangrijk

Azure SQL Database biedt alleen ondersteuning voor lezen vanuit Azure Blob Storage.

H. Toegang tot gegevens uit een CSV-bestand zonder een indelingsbestand

is alleen van toepassing op: 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'
);

Belangrijk

Het ODBC-stuurprogramma moet 64-bits zijn. Open het tabblad Stuurprogramma's van de Verbinding maken met een ODBC-gegevensbron (SQL Server Import and Export Wizard) toepassing in Windows om dit te controleren. Er is een 32-bits Microsoft Text Driver (*.txt, *.csv) die niet werkt met een 64-bits versie van sqlservr.exe.

Ik. Toegang krijgen tot gegevens uit een bestand dat is opgeslagen in Azure Blob Storage

Van toepassing op: alleen SQL Server 2017 (14.x) en latere versies.

In SQL Server 2017 (14.x) en latere versies wordt in het volgende voorbeeld een externe gegevensbron gebruikt die verwijst naar een container in een Azure-opslagaccount en een databasereferentie die is gemaakt voor een handtekening voor gedeelde toegang.

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

Zie Voorbeelden van bulktoegang tot gegevens in Azure Blob Storagevoor volledige OPENROWSET voorbeelden, waaronder het configureren van de referentie en externe gegevensbron.

J. Importeren in een tabel vanuit een bestand dat is opgeslagen in Azure Blob Storage

In het volgende voorbeeld ziet u hoe u de opdracht OPENROWSET gebruikt om gegevens uit een CSV-bestand te laden in een Azure Blob Storage-locatie waarop u de SAS-sleutel hebt gemaakt. De Azure Blob Storage-locatie is geconfigureerd als een externe gegevensbron. Hiervoor is een databasereferentie vereist met behulp van een Shared Access Signature die is versleuteld met behulp van een hoofdsleutel in de gebruikersdatabase.

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

Belangrijk

Azure SQL Database biedt alleen ondersteuning voor lezen vanuit Azure Blob Storage.

K. Een beheerde identiteit gebruiken voor een externe bron

van toepassing op: Azure SQL Managed Instance en Azure SQL Database

In het volgende voorbeeld wordt een referentie gemaakt met behulp van een beheerde identiteit, een externe bron gemaakt en vervolgens gegevens uit een CSV geladen die wordt gehost op de externe bron.

Maak eerst de referentie en geef blobopslag op als de externe bron:

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

Laad vervolgens gegevens uit het CSV-bestand dat wordt gehost in blobopslag:

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

Belangrijk

Azure SQL Database biedt alleen ondersteuning voor lezen vanuit Azure Blob Storage.

L. OPENROWSET gebruiken voor toegang tot verschillende Parquet-bestanden met behulp van S3-compatibele objectopslag

Van toepassing op: SQL Server 2022 (16.x) en latere versies.

In het volgende voorbeeld wordt gebruikgemaakt van toegang tot verschillende Parquet-bestanden vanaf verschillende locaties, die allemaal zijn opgeslagen op S3-compatibele objectopslag:

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. OPENROWSET gebruiken voor toegang tot verschillende Delta-bestanden vanuit Azure Data Lake Gen2

Van toepassing op: SQL Server 2022 (16.x) en latere versies.

In dit voorbeeld heet de gegevenstabelcontainer Contosoen bevindt zich in een Azure Data Lake Gen2-opslagaccount.

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;

Meer voorbeelden

Zie de volgende artikelen voor meer voorbeelden die het gebruik van INSERT...SELECT * FROM OPENROWSET(BULK...)weergeven:

DELETE (Transact-SQL)
FROM-component plus JOIN, APPLY, PIVOT (Transact-SQL)
bulksgewijs importeren en exporteren van gegevens (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)

Delen via

OPENROWSET (Transact-SQL)

Syntaxis

Argumenten

Algemene argumenten

'provider_name'

'gegevensbron'

'user_id'

'wachtwoord'

'provider_string'

<table_or_view>

'query'

BULKargumenten

BULK 'data_file'

Opties voor BULK-foutafhandeling

ERRORFILE = 'file_name'

ERRORFILE_DATA_SOURCE_NAME

MAXERRORS = maximum_errors

Opties voor BULKgegevensverwerking

FIRSTROW = first_row

LASTROW = last_row

ROWS_PER_BATCH = rows_per_batch

ORDER ( { kolom [ ASC | DESC ] } [ ,... n ] [ UNIEK ] )

UNIEK

SINGLE_BLOB

SINGLE_CLOB

SINGLE_NCLOB

Opties voor BULK-invoerbestandsindeling

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

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

FORMATFILE = 'format_file_path'

FIELDQUOTE = 'field_quote'

Opmerkingen

OPENROWSET gebruiken met de optie BULK

SQLCHAR-, SQLNCHAR- of SQLBINARY-gegevens bulksgewijs importeren

SQLXML-documenten bulksgewijs exporteren of importeren

Machtigingen

Voorbeelden

Een. OPENROWSET gebruiken met SELECT en de OLE DB-provider van de SQL Server-client

B. De Microsoft OLE DB-provider voor Jet gebruiken

C. OPENROWSET en een andere tabel in een INNER JOIN gebruiken

D. OPENROWSET gebruiken om bestandsgegevens BULKSGEWIJS IN TE VOEGEN in een varbinary(max)-kolom

E. De OPENROWSET BULK-provider gebruiken met een indelingsbestand om rijen op te halen uit een tekstbestand

F. Een indelingsbestand en codepagina opgeven

G. Toegang tot gegevens uit een CSV-bestand met een indelingsbestand

H. Toegang tot gegevens uit een CSV-bestand zonder een indelingsbestand

Ik. Toegang krijgen tot gegevens uit een bestand dat is opgeslagen in Azure Blob Storage

J. Importeren in een tabel vanuit een bestand dat is opgeslagen in Azure Blob Storage

K. Een beheerde identiteit gebruiken voor een externe bron

L. OPENROWSET gebruiken voor toegang tot verschillende Parquet-bestanden met behulp van S3-compatibele objectopslag

M. OPENROWSET gebruiken voor toegang tot verschillende Delta-bestanden vanuit Azure Data Lake Gen2

Meer voorbeelden

Verwante inhoud

Feedback

Aanvullende resources