OPENROWSET (Transact-SQL)

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:

• Azure SQL Database stöder endast läsning från Azure Blob Storage.
• Exempel på Azure SQL Managed Instance finns i Query data sources using OPENROWSET.
• Information och exempel med serverlösa SQL-pooler i Azure Synapse finns i Använda OPENROWSET med en serverlös SQL-pool i Azure Synapse Analytics.
• Dedikerade SQL-pooler i Azure Synapse stöder inte funktionen OPENROWSET.

Transact-SQL syntaxkonventioner

Syntax

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

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:

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.

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

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.

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

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.

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.

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.

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.

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.

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

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.

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.

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.

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)

  Viktig

  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.

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.

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.

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.

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

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.

USE Northwind;
GO

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

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

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

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

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

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

Formatfilen, values.fmt, beskriver kolumnerna i 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

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

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

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.

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.

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

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

gäller endast för: 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'
);

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.

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.

-- Optional: a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO

-- Optional: a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

-- Make sure that you don't have a leading ? in the SAS token, and that you
-- have at least read permission on the object that should be loaded srt=o&sp=r,
-- and that expiration period is valid (all dates are in UTC time)
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://****************.blob.core.windows.net/curriculum',
    -- CREDENTIAL is not required if a blob is configured for public (anonymous) access!
    CREDENTIAL = MyAzureBlobStorageCredential
);

INSERT INTO achievements
WITH (TABLOCK) (
    id,
    description
)
SELECT * FROM OPENROWSET(
    BULK 'csv/achievements.csv',
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FORMATFILE = 'csv/achievements-c.xml',
    FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
) AS DataFile;

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

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:

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

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:

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.

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:

• Exempel på massimport och export av XML-dokument (SQL Server)
• Behåll identitetsvärden vid massimport av data (SQL Server)
• Behåll null-värden eller standardvärden under massimport (SQL Server)
• Använd en formatfil för att massimportera data (SQL Server)
• Använd teckenformat för att importera eller exportera data (SQL Server)
• Använd en formatfil för att hoppa över en tabellkolumn (SQL Server)
• Använd en formatfil för att hoppa över ett datafält (SQL Server)
• Använd en formatfil för att mappa tabellkolumner till datafilfält (SQL Server)
• Fråga datakällor med OPENROWSET i Azure SQL Managed Instances

Relaterat innehåll

• DELETE (Transact-SQL)
• FROM-sats plus JOIN, APPLY, PIVOT (Transact-SQL)
• massimport och export av data (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)