Läs på engelska

Dela via

OPENROWSET (Transact-SQL)

  • Artikel

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Innehåller all anslutningsinformation som krävs för att komma åt fjärrdata från en OLE DB-datakälla. Den här metoden är ett alternativ till att komma åt tabeller på en länkad server och är en ad hoc-metod för att ansluta och komma åt fjärrdata med hjälp av OLE DB. Om du vill ha mer frekventa referenser till OLE DB-datakällor använder du länkade servrar i stället. Mer information finns i länkade servrar (databasmotorn). Funktionen OPENROWSET kan refereras till i FROM-satsen i en fråga som om den vore ett tabellnamn. Funktionen OPENROWSET kan också refereras till som måltabell för en INSERT, UPDATEeller DELETE-instruktion, med förbehåll för OLE DB-providerns funktioner. Även om frågan kan returnera flera resultatuppsättningar returnerar OPENROWSET bara den första.

OPENROWSET stöder även massåtgärder via en inbyggd BULK-provider som gör att data från en fil kan läsas och returneras som en raduppsättning.

Många exempel i den här artikeln gäller endast för SQL Server. Information och länkar till liknande exempel på andra plattformar:

Transact-SQL syntaxkonventioner

Syntax

OPENROWSET syntax används för att fråga externa datakällor:

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

OPENROWSET(BULK) syntax används för att läsa externa filer:

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

Argument

Vanliga argument

"provider_name"

En teckensträng som representerar det egna namnet (eller PROGID) för OLE DB-providern enligt vad som anges i registret. provider_name har inget standardvärde. Exempel på providernamn är Microsoft.Jet.OLEDB.4.0, SQLNCLIeller MSDASQL.

"datakälla"

En strängkonstant som motsvarar en viss OLE DB-datakälla. datakälla är egenskapen DBPROP_INIT_DATASOURCE som ska skickas till providerns IDBProperties gränssnitt för att initiera providern. Den här strängen innehåller vanligtvis namnet på databasfilen, namnet på en databasserver eller ett namn som providern förstår för att hitta databasen eller databaserna.

Datakälla kan vara filsökväg C:\SAMPLES\Northwind.mdb' för Microsoft.Jet.OLEDB.4.0 provider eller anslutningssträng Server=Seattle1;Trusted_Connection=yes; för SQLNCLI provider.

"user_id"

En strängkonstant som är användarnamnet som skickas till den angivna OLE DB-providern. user_id anger säkerhetskontexten för anslutningen och skickas som egenskapen DBPROP_AUTH_USERID för att initiera providern. user_id kan inte vara ett Inloggningsnamn för Microsoft Windows.

"lösenord"

En strängkonstant som är användarlösenordet som ska skickas till OLE DB-providern. lösenord skickas som egenskapen DBPROP_AUTH_PASSWORD när providern initieras. lösenord kan inte vara ett Microsoft Windows-lösenord.

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

"provider_string"

En providerspecifik anslutningssträng som skickas som egenskapen DBPROP_INIT_PROVIDERSTRING för att initiera OLE DB-providern. provider_string kapslar vanligtvis in all anslutningsinformation som krävs för att initiera providern. En lista över nyckelord som SQL Server Native Client OLE DB-providern känner igen finns i Initierings- och auktoriseringsegenskaper (inbyggd OLE DB-klientprovider).

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

<table_or_view>

Fjärrtabell eller vy som innehåller de data som OPENROWSET ska läsa. Det kan vara ett objekt med tre delar med följande komponenter:

  • katalog (valfritt) – namnet på katalogen eller databasen där det angivna objektet finns.
  • schema (valfritt) – namnet på schemat eller objektägaren för det angivna objektet.
  • objekt – det objektnamn som unikt identifierar det objekt som ska arbetas med.
SQL 
SELECT d.* FROM OPENROWSET(
    'SQLNCLI',
    'Server=Seattle1;Trusted_Connection=yes;',
    AdventureWorks2022.HumanResources.Department
) AS d;

"fråga"

En strängkonstant som skickas till och körs av providern. Den lokala instansen av SQL Server bearbetar inte den här frågan, men bearbetar frågeresultat som returneras av providern, en direktfråga. Direktfrågor är användbara när de används på leverantörer som inte gör sina tabelldata tillgängliga via tabellnamn, utan bara via ett kommandospråk. Direktfrågor stöds på fjärrservern, så länge frågeprovidern stöder OLE DB-kommandoobjektet och dess obligatoriska gränssnitt. Mer information finns i OLE DB-gränssnitt (SQL Server Native Client).

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

BULK-argument

Använder BULK rowset-providern för OPENROWSET för att läsa data från en fil. I SQL Server kan OPENROWSET läsa från en datafil utan att läsa in data i en måltabell. På så sätt kan du använda OPENROWSET med en grundläggande SELECT-instruktion.

Viktigt

Azure SQL Database stöder endast läsning från Azure Blob Storage.

Argumenten för alternativet BULK ger betydande kontroll över var data ska börja och sluta läsas, hur fel ska hanteras och hur data tolkas. Du kan till exempel ange att datafilen ska läsas som en radradsuppsättning med en rad av typen varbinary, varchareller nvarchar. Standardbeteendet beskrivs i de argumentbeskrivningar som följer.

Information om hur du använder alternativet BULK finns i avsnittet Kommentarer senare i den här artikeln. Information om de behörigheter som BULK alternativet kräver finns i avsnittet Behörigheter senare i den här artikeln.

Anteckning

När den används för att importera data med den fullständiga återställningsmodellen optimerar OPENROWSET (BULK ...) inte loggning.

Information om hur du förbereder data för massimport finns i Förbereda data för massexport eller import.

BULKdata_file

Den fullständiga sökvägen till den datafil vars data ska kopieras till måltabellen.

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

Från och med SQL Server 2017 (14.x) kan data_file finnas i Azure Blob Storage. Exempel finns i Exempel på massåtkomst till data i Azure Blob Storage.

Viktigt

Azure SQL Database stöder endast läsning från Azure Blob Storage.

Alternativ för BULK-felhantering

ERRORFILE = "file_name"

Anger den fil som används för att samla in rader som har formateringsfel och som inte kan konverteras till en OLE DB-raduppsättning. Dessa rader kopieras till den här felfilen från datafilen "som den är".

Felfilen skapas i början av kommandokörningen. Ett fel uppstår om filen redan finns. Dessutom skapas en kontrollfil som har tillägget .ERROR.txt. Den här filen refererar till varje rad i felfilen och ger feldiagnostik. När felen har korrigerats kan data läsas in.

Från och med SQL Server 2017 (14.x) kan error_file_path finnas i Azure Blob Storage.

ERRORFILE_DATA_SOURCE_NAME

Från och med SQL Server 2017 (14.x) är det här argumentet en namngiven extern datakälla som pekar på Azure Blob Storage-platsen för felfilen som innehåller fel som hittades under importen. Den externa datakällan måste skapas med hjälp av TYPE = BLOB_STORAGE. Mer information finns i CREATE EXTERNAL DATA SOURCE.

MAXERRORS = maximum_errors

Anger det maximala antalet syntaxfel eller icke-konforma rader, enligt definitionen i formatfilen, som kan inträffa innan OPENROWSET utlöser ett undantag. Tills MAXERRORS har nåtts ignorerar OPENROWSET varje felaktig rad, läser inte in den och räknar den felaktiga raden som ett fel.

Standardvärdet för maximum_errors är 10.

Anteckning

MAX_ERRORS gäller inte för CHECK begränsningar, eller för att konvertera pengar och storint datatyper.

Alternativ för BULK-databearbetning

FIRSTROW = first_row

Anger numret på den första raden som ska läsas in. Standardvärdet är 1. Detta anger den första raden i den angivna datafilen. Radnumren bestäms genom att radavslutarna räknas. FIRSTROW är 1-baserad.

LASTROW = last_row

Anger numret på den sista raden som ska läsas in. Standardvärdet är 0. Detta anger den sista raden i den angivna datafilen.

ROWS_PER_BATCH = rows_per_batch

Anger det ungefärliga antalet rader med data i datafilen. Det här värdet ska ha samma ordning som det faktiska antalet rader.

OPENROWSET importerar alltid en datafil som en enda batch. Men om du anger rows_per_batch med ett värde > 0 använder frågeprocessorn värdet för rows_per_batch som ett tips för att allokera resurser i frågeplanen.

Som standard är ROWS_PER_BATCH okänt. Att ange ROWS_PER_BATCH = 0 är detsamma som att utelämna ROWS_PER_BATCH.

ORDER ( { kolumn [ ASC | DESC ] } [ ,... n ] [ UNIK ] )

Ett valfritt tips som anger hur data i datafilen sorteras. Som standard förutsätter massåtgärden att datafilen är osorterad. Prestanda kan förbättras om frågeoptimeraren kan utnyttja ordningen för att generera en effektivare frågeplan. Följande lista innehåller exempel på när du anger en sortering kan vara fördelaktigt:

  • Infoga rader i en tabell som har ett grupperat index, där raduppsättningsdata sorteras på den klustrade indexnyckeln.
  • Koppla raduppsättningen till en annan tabell, där sorterings- och kopplingskolumnerna matchar.
  • Aggregera raduppsättningsdata efter sorteringskolumnerna.
  • Använda raduppsättningen som en källtabell i FROM-satsen för en fråga, där sorterings- och kopplingskolumnerna matchar.

UNIK

Anger att datafilen inte har duplicerade poster.

Om de faktiska raderna i datafilen inte sorteras enligt den angivna ordningen, eller om UNIQUE tips anges och dubbletter av nycklar finns, returneras ett fel.

Kolumnalias krävs när ORDER används. Kolumnaliaslistan måste referera till den härledda tabell som används av BULK-satsen. Kolumnnamnen som anges i ORDER-satsen refererar till den här kolumnaliaslistan. Det går inte att ange stora värdetyper (varchar(max), nvarchar(max), varbinary(max)och xml) och stora objekttyper (LOB) (text, ntextoch bild) inte anges.

SINGLE_BLOB

Returnerar innehållet i data_file som en radradsuppsättning med en kolumn av typen varbinary(max).

Viktigt

Vi rekommenderar att du importerar XML-data endast med hjälp av alternativet SINGLE_BLOB i stället för att SINGLE_CLOB och SINGLE_NCLOB, eftersom endast SINGLE_BLOB stöder alla Konverteringar av Windows-kodning.

SINGLE_CLOB

Genom att läsa data_file som ASCII returnerar innehållet som en radradsuppsättning med en rad av typen varchar(max), med hjälp av sortering av den aktuella databasen.

SINGLE_NCLOB

Genom att läsa data_file som Unicode returnerar innehållet som en radradsuppsättning med en enda kolumn av typen nvarchar(max), med hjälp av sortering av den aktuella databasen.

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

Alternativ för BULK-indatafilformat

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

Anger kodsidan för data i datafilen. CODEPAGE är endast relevant om data innehåller tecken, varchareller text kolumner med teckenvärden som är mer än 127 eller mindre än 32.

Viktigt

CODEPAGE stöds inte i Linux.

Anteckning

Vi rekommenderar att du anger ett sorteringsnamn för varje kolumn i en formatfil, förutom när du vill att alternativet 65001 ska ha prioritet framför specifikationen för sorterings-/kodsidan.

CODEPAGE-värde Beskrivning
ACP Konverterar kolumner med tecken, varchareller text datatyp från kodsidan ANSI/Microsoft Windows (ISO 1252) till SQL Server-kodsidan.
OEM (standard) Konverterar kolumner med tecken, varchareller text datatyp från systemets OEM-kodsida till SQL Server-kodsidan.
RAW Ingen konvertering sker från en kodsida till en annan. Det här är det snabbaste alternativet.
code_page Anger den källkodssida där teckendata i datafilen är kodade. till exempel 850.

Viktiga versioner före SQL Server 2016 (13.x) stöder inte kodsidan 65001 (UTF-8-kodning).

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

Från och med SQL Server 2017 (14.x) anger det här argumentet en fil med kommaavgränsade värden som är kompatibel med RFC 4180 standard.

Från och med SQL Server 2022 (16.x) stöds både Parquet- och Delta-format.

SQL 
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'

Anger den fullständiga sökvägen för en formatfil. SQL Server stöder två typer av formatfiler: XML och icke-XML.

En formatfil krävs för att definiera kolumntyper i resultatuppsättningen. Det enda undantaget är när SINGLE_CLOB, SINGLE_BLOBeller SINGLE_NCLOB anges. I så fall krävs inte formatfilen.

Information om formatfiler finns i Använda en formatfil för att massimportera data (SQL Server).

Från och med SQL Server 2017 (14.x) kan format_file_path finnas i Azure Blob Storage. Exempel finns i Exempel på massåtkomst till data i Azure Blob Storage.

FIELDQUOTE = 'field_quote'

Från och med SQL Server 2017 (14.x) anger det här argumentet ett tecken som används som citattecken i CSV-filen. Om det inte anges används citattecknet (") som citattecken enligt definitionen i RFC 4180 standard.

Anmärkningar

OPENROWSET kan endast användas för att komma åt fjärrdata från OLE DB-datakällor när DisallowAdhocAccess registeralternativ uttryckligen anges till 0 för den angivna providern, och alternativet avancerad konfiguration för Ad Hoc Distributed Queries är aktiverat. När dessa alternativ inte har angetts tillåter standardbeteendet inte ad hoc-åtkomst.

När du kommer åt ole db-fjärrdatakällor delegeras inte inloggningsidentiteten för betrodda anslutningar automatiskt från servern där klienten är ansluten till den server som efterfrågas. Autentiseringsdelegering måste konfigureras.

Katalog- och schemanamn krävs om OLE DB-providern stöder flera kataloger och scheman i den angivna datakällan. Värden för katalog och schema kan utelämnas när OLE DB-providern inte stöder dem. Om providern endast stöder schemanamnett tvådelade namn på formuläret schema .objekt måste anges. Om providern endast stöder katalognamn ett namn i tre delar av formuläret.schema.objekt måste anges. Tredelade namn måste anges för direktfrågor som använder SQL Server Native Client OLE DB-providern. Mer information finns i Transact-SQL syntaxkonventioner.

OPENROWSET accepterar inte variabler för sina argument.

Alla anrop till OPENDATASOURCE, OPENQUERYeller OPENROWSET i FROM-satsen utvärderas separat och oberoende av alla anrop till dessa funktioner som används som mål för uppdateringen, även om identiska argument skickas till de två anropen. I synnerhet har filter- eller kopplingsvillkor som tillämpas på resultatet av ett av dessa anrop ingen effekt på resultatet av det andra.

Använd OPENROWSET med alternativet BULK

Följande Transact-SQL förbättringar stöder funktionen OPENROWSET(BULK...):

  • En FROM-sats som används med SELECT kan anropa OPENROWSET(BULK...) i stället för ett tabellnamn, med fullständig SELECT funktioner.

    OPENROWSET med alternativet BULK kräver ett korrelationsnamn, även kallat intervallvariabel eller alias, i FROM-satsen. Kolumnalias kan anges. Om en kolumnaliaslista inte har angetts måste formatfilen ha kolumnnamn. Om du anger kolumnalias åsidosätts kolumnnamnen i formatfilen, till exempel:

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

    Viktigt

    Om du inte lägger till AS <table_alias> uppstår felet: Msg 491, Nivå 16, Delstat 1, Rad 20 Ett korrelationsnamn måste anges för massraderuppsättningen i from-satsen.

  • En SELECT...FROM OPENROWSET(BULK...)-instruktion frågar data i en fil direkt, utan att importera data till en tabell. SELECT...FROM OPENROWSET(BULK...)-instruktioner kan också visa masskolumnalias med hjälp av en formatfil för att ange kolumnnamn och även datatyper.

  • Om du använder OPENROWSET(BULK...) som en källtabell i en INSERT- eller MERGE-instruktion importeras data från en datafil till en SQL Server-tabell. Mer information finns i Använda BULK INSERT eller OPENROWSET(BULK...) för att importera data till SQL Server.

  • När alternativet OPENROWSET BULK används med en INSERT-instruktion stöder BULK-satsen tabelltips. Förutom vanliga tabelltips, till exempel TABLOCK, kan BULK-satsen acceptera följande specialiserade tabelltips: IGNORE_CONSTRAINTS (ignorerar endast begränsningarna CHECK och FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTSoch KEEPIDENTITY. Mer information finns i Tabelltips (Transact-SQL).

    Information om hur du använder INSERT...SELECT * FROM OPENROWSET(BULK...)-instruktioner finns i massimport och export av data (SQL Server). Information om när radinfogningsåtgärder som utförs av massimport loggas i transaktionsloggen finns i Krav för minimal loggning i massimport.

Anteckning

När du använder OPENROWSETär det viktigt att förstå hur SQL Server hanterar personifiering. Information om säkerhetsöverväganden finns i Använda BULK INSERT eller OPENROWSET(BULK...) för att importera data till SQL Server.

Massimport av SQLCHAR-, SQLNCHAR- eller SQLBINARY-data

OPENROWSET(BULK...) förutsätter att den maximala längden på SQLCHAR, SQLNCHAReller SQLBINARY data inte överskrider 8 000 byte om de inte anges. Om data som importeras finns i ett LOB-datafält som innehåller varchar(max), nvarchar(max)eller varbinary(max) objekt som överskrider 8 000 byte, måste du använda en XML-formatfil som definierar den maximala längden för datafältet. Om du vill ange maximal längd redigerar du formatfilen och deklarerar attributet MAX_LENGTH.

Anteckning

En automatiskt genererad formatfil anger inte längden eller maxlängden för ett LOB-fält. Du kan dock redigera en formatfil och ange längden eller maxlängden manuellt.

Massexportera eller importera SQLXML-dokument

Om du vill massexportera eller importera SQLXML-data använder du någon av följande datatyper i formatfilen.

Datatyp Effekt
SQLCHAR eller SQLVARYCHAR Data skickas på klientkodsidan eller på kodsidan som är underförstådd av sorteringen.
SQLNCHAR eller SQLNVARCHAR Data skickas som Unicode.
SQLBINARY eller SQLVARYBIN Data skickas utan konvertering.

Behörigheter

OPENROWSET behörigheter bestäms av behörigheterna för det användarnamn som skickas till OLE DB-providern. För att kunna använda alternativet BULK krävs ADMINISTER BULK OPERATIONS eller ADMINISTER DATABASE BULK OPERATIONS behörighet.

Exempel

Det här avsnittet innehåller allmänna exempel som visar hur du använder OPENROWSET.

A. Använda OPENROWSET med SELECT och SQL Server Native Client OLE DB-providern

gäller endast för: SQL Server.

Den interna SQL Server-klienten (ofta förkortad SNAC) har tagits bort från SQL Server 2022 (16.x) och SQL Server Management Studio 19 (SSMS). Både SQL Server Native Client OLE DB-providern (SQLNCLI eller SQLNCLI11) och den äldre Microsoft OLE DB-providern för SQL Server (SQLOLEDB) rekommenderas inte för ny utveckling. Växla till den nya Microsoft OLE DB Driver (MSOLEDBSQL) för SQL Server framöver.

I följande exempel används SQL Server Native Client OLE DB-providern för att komma åt tabellen HumanResources.Department i AdventureWorks2022-databasen på fjärrservern Seattle1. (Använd SQLNCLI och SQL Server omdirigeras till den senaste versionen av SQL Server Native Client OLE DB Provider.) En SELECT-instruktion används för att definiera raduppsättningen som returneras. Providersträngen innehåller nyckelorden Server och Trusted_Connection. Dessa nyckelord identifieras av SQL Server Native Client OLE DB-providern.

SQL 
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. Använda Microsoft OLE DB-providern för Jet

gäller endast för: SQL Server.

I följande exempel används tabellen Customers i Microsoft Access Northwind-databasen via Microsoft OLE DB-providern för Jet.

Anteckning

Det här exemplet förutsätter att Microsoft Access är installerat. Om du vill köra det här exemplet måste du installera Northwind-databasen.

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

Viktigt

Azure SQL Database stöder endast läsning från Azure Blob Storage.

C. Använda OPENROWSET och en annan tabell i en INRE KOPPLING

gäller endast för: SQL Server.

I följande exempel markeras alla data från tabellen Customers från den lokala instansen av SQL Server Northwind databas och från tabellen Orders från Access Northwind-databasen som lagras på samma dator.

Anteckning

Det här exemplet förutsätter att Access är installerat. Om du vill köra det här exemplet måste du installera Northwind-databasen.

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

Viktigt

Azure SQL Database stöder endast läsning från Azure Blob Storage.

D. Använd OPENROWSET för att MASSINFOGA fildata i en kolumn med varbinary(max)

gäller endast för: SQL Server.

I följande exempel skapas en liten tabell i demonstrationssyfte och fildata infogas från en fil med namnet Text1.txt som finns i C: rotkatalog till en varbinary(max) kolumn.

SQL 
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

Viktigt

Azure SQL Database stöder endast läsning från Azure Blob Storage.

E. Använd OPENROWSET BULK-providern med en formatfil för att hämta rader från en textfil

gäller endast för: SQL Server.

I följande exempel används en formatfil för att hämta rader från en flikavgränsad textfil, values.txt som innehåller följande data:

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

Formatfilen, values.fmt, beskriver kolumnerna i values.txt:

Output 
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

Den här frågan hämtar dessa data:

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

Viktigt

Azure SQL Database stöder endast läsning från Azure Blob Storage.

F. Ange en formatfil och kodsida

gäller endast för: SQL Server.

I följande exempel visas hur du använder alternativ för både formatfilen och kodsidan samtidigt.

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

G. Få åtkomst till data från en CSV-fil med en formatfil

gäller endast för: SQL Server 2017 (14.x) och senare versioner.

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

Viktigt

Azure SQL Database stöder endast läsning från Azure Blob Storage.

H. Komma åt data från en CSV-fil utan en formatfil

gäller endast för: SQL Server.

SQL 
SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14.CTP1_1\MSSQL\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB
) AS DATA;
SQL 
SELECT *
FROM OPENROWSET('MSDASQL',
    'Driver={Microsoft Access Text Driver (*.txt, *.csv)}',
    'SELECT * FROM E:\Tlog\TerritoryData.csv'
);

Viktigt

ODBC-drivrutinen ska vara 64-bitars. Öppna fliken Drivrutiner i Anslut till en ODBC-datakälla (guiden importera och exportera SQL Server) program i Windows för att verifiera detta. Det finns 32-bitars Microsoft Text Driver (*.txt, *.csv) som inte fungerar med en 64-bitarsversion av sqlservr.exe.

Jag. Komma åt data från en fil som lagras i Azure Blob Storage

gäller endast för: SQL Server 2017 (14.x) och senare versioner.

I SQL Server 2017 (14.x) och senare versioner använder följande exempel en extern datakälla som pekar på en container i ett Azure-lagringskonto och en databasomfattande autentiseringsuppgift som skapats för en signatur för delad åtkomst.

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

Fullständiga OPENROWSET exempel, inklusive konfiguration av autentiseringsuppgifter och extern datakälla, finns i Exempel på massåtkomst till data i Azure Blob Storage.

J. Importera till en tabell från en fil som lagras i Azure Blob Storage

I följande exempel visas hur du använder kommandot OPENROWSET för att läsa in data från en csv-fil på en Azure Blob Storage-plats där du skapade SAS-nyckeln. Azure Blob Storage-platsen är konfigurerad som en extern datakälla. Detta kräver en databasomfattande autentiseringsuppgift med hjälp av en signatur för delad åtkomst som krypteras med hjälp av en huvudnyckel i användardatabasen.

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

Viktigt

Azure SQL Database stöder endast läsning från Azure Blob Storage.

K. Använda en hanterad identitet för en extern källa

gäller för: Azure SQL Managed Instance och Azure SQL Database

I följande exempel skapas en autentiseringsuppgift med hjälp av en hanterad identitet, skapar en extern källa och läser sedan in data från en CSV som finns på den externa källan.

Skapa först autentiseringsuppgifterna och ange bloblagring som extern källa:

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

Läs sedan in data från CSV-filen som finns på Blob Storage:

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

Viktigt

Azure SQL Database stöder endast läsning från Azure Blob Storage.

L. Använda OPENROWSET för att komma åt flera Parquet-filer med S3-kompatibel objektlagring

gäller för: SQL Server 2022 (16.x) och senare versioner.

I följande exempel används åtkomst till flera Parquet-filer från olika platser, som alla lagras på S3-kompatibel objektlagring:

SQL 
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. Använd OPENROWSET för att komma åt flera Delta-filer från Azure Data Lake Gen2

gäller för: SQL Server 2022 (16.x) och senare versioner.

I det här exemplet heter datatabellcontainern Contosooch finns på ett Azure Data Lake Gen2-lagringskonto.

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

Fler exempel

Fler exempel som visar hur du använder INSERT...SELECT * FROM OPENROWSET(BULK...)finns i följande artiklar:

Relaterat innehåll

gäller för:Warehouse i Microsoft Fabric

Funktionen T-SQL OPENROWSET läser ett innehåll i en fil i Azure Data Lake Storage. Du kan läsa text-/CSV- eller Parquet-filformat.

Funktionen OPENROWSET läser data från en fil och returnerar dem som en raduppsättning. Funktionen OPENROWSET kan refereras till i FROM-satsen i en fråga som om den vore ett tabellnamn.

Anteckning

Funktionen OPENROWSET finns för närvarande i förhandsversion för Microsoft Fabric.

Den här artikeln gäller endast för Microsoft Fabric Warehouse. Det finns funktionella skillnader mellan funktionen OPENROWSET i Fabric Warehouse och SQL Analytics-slutpunktsobjekt.

Information och länkar till liknande exempel på andra plattformar:

Syntax

syntaxsql 
SELECT <columns>
FROM OPENROWSET(
    BULK 'https://<storage>.blob.core.windows.net/path/folder1=*/folder2=*/filename.parquet'
    [, FORMAT = ('PARQUET' | 'CSV') ]

    -- Text formatting options
    [, DATAFILETYPE = {'char' | 'widechar' }     ]
    [, CODEPAGE = {'ACP' | 'OEM' | 'raw' | '<code_page>' } ]

    -- Text/CSV formatting options
    [, ROWTERMINATOR = 'row_terminator' ]
    [, FIELDTERMINATOR =  'field_terminator' ]
    [, FIELDQUOTE = 'string_delimiter' ]
    [, ESCAPECHAR = 'escape_char' ]
    [, HEADER_ROW = [true|false] ]
    [, FIRSTROW = first_row ]
    [, LASTROW = last_row ]

    -- execution options
    [, ROWS_PER_BATCH=number_of_rows]
) 
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]
AS <alias>

Argument

BULK "data_file"

URI:n för de datafiler vars data ska läsas och returneras som raduppsättning. URI:n kan referera till Azure Data Lake Storage eller Azure Blob Storage.

URI:n kan innehålla * tecken som representerar valfri teckensekvens och gör att OPENROWSET kan matcha URI:n med mönstret.

Alternativ för BULK-indatafilformat

FORMAT = { 'CSV' | "PARQUET" }

Anger formatet på den refererade filen. Om filtillägget i sökvägen med .csv, .parquet eller .parq behöver inte alternativet FORMAT anges. Till exempel:

SQL 
SELECT *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet'
    FORMAT = N'CSV') AS cars;

DATAFILETYPE = { 'char' | "widechar" }

Anger att OPENROWSET(BULK) ska läsa filinnehållet single-byte (ASCII, UTF8) eller multi-byte (UTF16).

DATAFILETYPE-värde Alla data som representeras i:
tecken (standard) Teckenformat.

Mer information finns i Använda teckenformat för att importera eller exportera data.
widechar Unicode-tecken.

Mer information finns i Använda Unicode-teckenformat för att importera eller exportera data.

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

Anger kodsidan för data i datafilen. CODEPAGE är endast relevant om data innehåller tecken, varchareller text kolumner med teckenvärden som är mer än 127 eller mindre än 32.

CODEPAGE-värde Beskrivning
ACP Konverterar kolumner med tecken, varchareller text datatyp från kodsidan ANSI/Microsoft Windows (ISO 1252) till SQL Server-kodsidan.
OEM (standard) Konverterar kolumner med tecken, varchareller text datatyp från systemets OEM-kodsida till SQL Server-kodsidan.
RAW Ingen konvertering sker från en kodsida till en annan. Det här är det snabbaste alternativet.
code_page Anger den källkodssida där teckendata i datafilen är kodade. till exempel 850.

Viktiga versioner före SQL Server 2016 (13.x) stöder inte kodsidan 65001 (UTF-8-kodning).

Formateringsalternativ för text/CSV

ROWTERMINATOR = "row_terminator"

Anger radavgränsaren som ska användas för tecken och widechar datafiler. Standardradavslutaren är \r\n (nytt radtecken). Mer information finns i Ange fält och radavgränsare.

FIELDTERMINATOR = "field_terminator"

Anger fältavgränsaren som ska användas för tecken och widechar datafiler. Standardfältets avslutare är \t (tabbtecken). Mer information finns i Ange fält och radavgränsare.

FIELDQUOTE = 'field_quote'

Anger ett tecken som används som citattecken i CSV-filen. Om det inte anges används citattecknet (") som citattecken enligt definitionen i RFC 4180 standard.

ESCAPE_CHAR = "char"

Anger tecknet i filen som används för att undkomma sig själv och alla avgränsarvärden i filen. Om escape-tecknet följs av ett annat värde än sig självt, eller något av avgränsarvärdena, tas escape-tecknet bort när värdet läss.

ESCAPECHAR-parametern tillämpas oavsett om FIELDQUOTE är aktiverat eller inte. Den kommer inte att användas för att undkomma citattecknet. Citattecknet måste vara undantaget med ett annat citattecken. Citattecken kan endast visas inom kolumnvärdet om värdet kapslas in med citattecken.

HEADER_ROW = { TRUE | FALSE }

Anger om en CSV-fil innehåller rubrikrad. Standardvärdet är FALSE. Stöds i PARSER_VERSION='2.0'. Om värdet är SANT läss kolumnnamnen från den första raden enligt argumentet FIRSTROW. Om TRUE och schema anges med HJÄLP av WITH utförs bindningen av kolumnnamn efter kolumnnamn, inte ordningstalspositioner.

FIRSTROW = first_row

Anger numret på den första raden som ska läsas in. Standardvärdet är 1. Detta anger den första raden i den angivna datafilen. Radnumren bestäms genom att radavslutarna räknas. FIRSTROW är 1-baserad.

LASTROW = last_row

Anger numret på den sista raden som ska läsas in. Standardvärdet är 0. Detta anger den sista raden i den angivna datafilen.

Körningsalternativ

ROWS_PER_BATCH = rows_per_batch

Anger det ungefärliga antalet rader med data i datafilen. Det här värdet ska ha samma ordning som det faktiska antalet rader.

Som standard beräknas ROWS_PER_BATCH baserat på filegenskaper (antal filer, filstorlekar, storleken på de returnerade datatyperna). Att ange ROWS_PER_BATCH = 0 är detsamma som att utelämna ROWS_PER_BATCH.

MED schema

Schemat WITH anger de kolumner som definierar resultatuppsättningen för funktionen OPENROWSET. Den innehåller kolumndefinitioner för varje kolumn som returneras som ett resultat och beskriver de mappningsregler som binder de underliggande filkolumnerna till kolumnerna i resultatuppsättningen.

<column_name>

Namnet på kolumnen som ska returneras i resultatraduppsättningen. Data för den här kolumnen läss från den underliggande filkolumnen med samma namn, om de inte åsidosätts av <column_path> eller <column_ordinal>.

<column_type>

T-SQL-typen för kolumnen i resultatuppsättningen. Värdena från den underliggande filen konverteras till den här typen när OPENROWSET returnerar resultatet.

<column_path>

En punktavgränsad sökväg (till exempel $.description.location.lat) som används för att referera till kapslade fält i komplexa typer som Parquet.

<column_ordinal>

Ett tal som representerar det fysiska indexet för kolumnen som ska mappas till kolumnen i WITH-satsen.

Anmärkningar

De funktioner som stöds i den aktuella förhandsversionen sammanfattas i tabellen:

Egenskap Understödd Inte tillgängligt
Filformat Parquet, CSV Delta, Azure Cosmos DB
Autentisering EntraID-genomströmning, offentlig lagring SAS/SAK, SPN, Hanterad åtkomst
Förvaring Azure Blob Storage, Azure Data Lake Storage OneLake
Ett alternativ Endast fullständig absolut URI i OPENROWSET DATA_SOURCE
Partitionering Du kan använda funktionen filepath() i en fråga.

Exempel

Läsa en parquet-fil från Azure Blob Storage

I följande exempel kan du se hur du läser 100 rader från en Parquet-fil:

SQL 
SELECT TOP 100 * 
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet'
) AS data;

Läsa en anpassad CSV-fil

I följande exempel kan du se hur du läser rader från en CSV-fil med en rubrikrad och uttryckligen angivna avslutstecken som separerar rader och fält:

SQL 
SELECT *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv',
 HEADER_ROW = TRUE,
 ROW_TERMINATOR = '\n',
 FIELD_TERMINATOR = ',') 
AS data;

Ange filkolumnschemat när du läser en fil

I följande exempel kan du se hur du uttryckligen anger schemat för raden som ska returneras som ett resultat av funktionen OPENROWSET:

SQL 
SELECT *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet') 
WITH (
        updated DATE
        ,confirmed INT
        ,deaths INT
        ,iso2 VARCHAR(8000)
        ,iso3 VARCHAR(8000)
        ) AS covid_data;

Läsa partitionerade datauppsättningar

I följande exempel kan du se hur du använder funktionen filepath() för att läsa delarna av URI:n från den matchade filsökvägen:

SQL 
SELECT TOP 10 
  files.filepath(2) AS area
, files.*
FROM OPENROWSET(
BULK 'https://synapseaisolutionsa.blob.core.windows.net/public/NYC_Property_Sales_Dataset/*_*.csv',
 HEADER_ROW = TRUE) 
AS files
WHERE files.filepath(1) = '2009';

Relaterat innehåll