OPENROWSET (T-SQL)

Artikel
01/30/2024

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance

Menyertakan semua informasi koneksi yang diperlukan untuk mengakses data jarak jauh dari sumber data OLE DB. Metode ini adalah alternatif untuk mengakses tabel di server yang ditautkan dan merupakan metode satu kali ad hoc untuk menyambungkan dan mengakses data jarak jauh dengan menggunakan OLE DB. Untuk referensi yang lebih sering ke sumber data OLE DB, gunakan server tertaut sebagai gantinya. Untuk informasi selengkapnya, lihat Server Tertaut (Mesin Database). Fungsi OPENROWSET dapat dirujuk dalam FROM klausa kueri seolah-olah itu adalah nama tabel. Fungsi ini OPENROWSET juga dapat direferensikan sebagai tabel target dari INSERTpernyataan , , UPDATEatau DELETE , yang tunduk pada kemampuan penyedia OLE DB. Meskipun kueri mungkin mengembalikan beberapa kumpulan hasil, OPENROWSET hanya mengembalikan yang pertama.

OPENROWSET juga mendukung operasi massal melalui penyedia bawaan BULK yang memungkinkan data dari file dibaca dan dikembalikan sebagai set baris.

Banyak contoh dalam artikel ini hanya berlaku untuk SQL Server. Detail dan tautan ke contoh serupa di platform lain:

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.
Untuk contoh tentang Azure SQL Managed Instance, lihat Mengkueri sumber data menggunakan OPENROWSET.
Untuk informasi dan contoh dengan kumpulan SQL tanpa server di Azure Synapse, lihat Cara menggunakan OPENROWSET menggunakan kumpulan SQL tanpa server di Azure Synapse Analytics.
Kumpulan SQL khusus di Azure Synapse tidak mendukung fungsi tersebut OPENROWSET .

Konvensi sintaks transact-SQL

Sintaksis

OPENROWSET
( { 'provider_name'
    , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
    , {   <table_or_view> | 'query' }
   | BULK 'data_file' ,
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )

<table_or_view> ::= [ catalog. ] [ schema. ] object

<bulk_options> ::=
   [ , DATASOURCE = 'data_source_name' ]
   [ , ERRORFILE = 'file_name' ]
   [ , ERRORFILE_DATA_SOURCE = 'data_source_name' ]
   [ , MAXERRORS = maximum_errors ]
   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]
   [ , ROWS_PER_BATCH = rows_per_batch ]
   [ , ORDER ( { column [ ASC | DESC ] } [ , ...n ] ) [ UNIQUE ] ]

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

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

Argumen umum

'provider_name'

String karakter yang mewakili nama yang mudah diingat (atau PROGID) dari penyedia OLE DB seperti yang ditentukan dalam registri. provider_name tidak memiliki nilai default. Contoh nama penyedia adalah Microsoft.Jet.OLEDB.4.0, , SQLNCLIatau MSDASQL.

'sumber data'

Konstanta string yang sesuai dengan sumber data OLE DB tertentu. sumber data adalah properti yang DBPROP_INIT_DATASOURCE akan diteruskan ke IDBProperties antarmuka penyedia untuk menginisialisasi penyedia. Biasanya, string ini mencakup nama file database, nama server database, atau nama yang dipahami penyedia untuk menemukan database atau database.

Sumber data dapat berupa jalur C:\SAMPLES\Northwind.mdb' file untuk Microsoft.Jet.OLEDB.4.0 penyedia, atau string koneksi Server=Seattle1;Trusted_Connection=yes; untuk SQLNCLI penyedia.

'user_id'

Konstanta string yang merupakan nama pengguna yang diteruskan ke penyedia OLE DB yang ditentukan. user_id menentukan konteks keamanan untuk koneksi dan diteruskan sebagai DBPROP_AUTH_USERID properti untuk menginisialisasi penyedia. user_id tidak dapat menjadi nama masuk Microsoft Windows.

'kata sandi'

Konstanta string yang merupakan kata sandi pengguna yang akan diteruskan ke penyedia OLE DB. kata sandi diteruskan sebagai DBPROP_AUTH_PASSWORD properti saat menginisialisasi penyedia. kata sandi tidak dapat berupa kata sandi Microsoft Windows.

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

'provider_string'

String koneksi khusus penyedia yang diteruskan sebagai DBPROP_INIT_PROVIDERSTRING properti untuk menginisialisasi penyedia OLE DB. provider_string biasanya merangkum semua informasi koneksi yang diperlukan untuk menginisialisasi penyedia. Untuk daftar kata kunci yang dikenal penyedia OLE DB Klien Asli SQL Server, lihat Properti Inisialisasi dan Otorisasi (Penyedia OLE DB Klien Asli).

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

<table_or_view>

Tabel atau tampilan jarak jauh yang berisi data yang OPENROWSET harus dibaca. Ini bisa menjadi objek tiga bagian-nama dengan komponen berikut:

katalog (opsional) - nama katalog atau database tempat objek yang ditentukan berada.
skema (opsional) - nama skema atau pemilik objek untuk objek yang ditentukan.
objek - nama objek yang secara unik mengidentifikasi objek untuk dikerjakan.

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

'kueri'

Konstanta string dikirim ke dan dijalankan oleh penyedia. Instans lokal SQL Server tidak memproses kueri ini, tetapi memproses hasil kueri yang dikembalikan oleh penyedia, kueri pass-through. Kueri pass-through berguna saat digunakan pada penyedia yang tidak menyediakan data tabular mereka melalui nama tabel, tetapi hanya melalui bahasa perintah. Kueri pass-through didukung di server jarak jauh, selama penyedia kueri mendukung objek Perintah OLE DB dan antarmuka wajibnya. Untuk informasi selengkapnya, lihat Antarmuka SQL Server Native Client (OLE DB).

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

Argumen MASSAL

BULK Menggunakan penyedia set baris untuk OPENROWSET membaca data dari file. Di SQL Server, OPENROWSET dapat membaca dari file data tanpa memuat data ke dalam tabel target. Ini memungkinkan Anda menggunakan OPENROWSET dengan pernyataan dasar SELECT .

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

Argumen BULK opsi memungkinkan kontrol yang signifikan atas tempat memulai dan mengakhiri membaca data, cara menangani kesalahan, dan bagaimana data ditafsirkan. Misalnya, Anda dapat menentukan bahwa file data dibaca sebagai baris tunggal, himpunan baris kolom tunggal dari jenis varbinary, varchar, atau nvarchar. Perilaku default dijelaskan dalam deskripsi argumen yang mengikuti.

Untuk informasi tentang cara menggunakan BULK opsi, lihat bagian Keterangan nanti di artikel ini. Untuk informasi tentang izin yang BULK diperlukan opsi, lihat bagian Izin , nanti di artikel ini.

Catatan

Saat digunakan untuk mengimpor data dengan model pemulihan penuh, OPENROWSET (BULK ...) tidak mengoptimalkan pengelogan.

Untuk informasi tentang menyiapkan data untuk impor massal, lihat Menyiapkan data untuk ekspor atau impor massal.

'data_file' MASSAL

Jalur lengkap file data yang datanya akan disalin ke dalam tabel target.

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

Dimulai dengan SQL Server 2017 (14.x), data_file dapat berada di Azure Blob Storage. Misalnya, lihat Contoh akses massal ke data di Azure Blob Storage.

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

Opsi penanganan kesalahan MASSAL

ERRORFILE ='file_name'

Menentukan file yang digunakan untuk mengumpulkan baris yang memiliki kesalahan pemformatan dan tidak dapat dikonversi ke kumpulan baris OLE DB. Baris ini disalin ke dalam file kesalahan ini dari file data "apa adanya."

File kesalahan dibuat pada awal eksekusi perintah. Kesalahan dimunculkan jika file sudah ada. Selain itu, file kontrol yang memiliki ekstensi . ERROR.txt dibuat. File ini mereferensikan setiap baris dalam file kesalahan dan menyediakan diagnostik kesalahan. Setelah kesalahan dikoreksi, data dapat dimuat.

Dimulai dengan SQL Server 2017 (14.x), error_file_path dapat berada di Azure Blob Storage.

ERRORFILE_DATA_SOURCE_NAME

Dimulai dengan SQL Server 2017 (14.x), argumen ini adalah sumber data eksternal bernama yang menunjuk ke lokasi penyimpanan Azure Blob dari file kesalahan yang akan berisi kesalahan yang ditemukan selama impor. Sumber data eksternal harus dibuat menggunakan TYPE = BLOB_STORAGE. Untuk informasi selengkapnya, lihat MEMBUAT SUMBER DATA EKSTERNAL.

MAXERRORS = maximum_errors

Menentukan jumlah maksimum kesalahan sintaks atau baris nonkonformasi, seperti yang didefinisikan dalam file format, yang dapat terjadi sebelum OPENROWSET melemparkan pengecualian. Hingga MAXERRORS tercapai, OPENROWSET mengabaikan setiap baris yang buruk, tidak memuatnya, dan menghitung baris yang buruk sebagai satu kesalahan.

Default untuk maximum_errors adalah 10.

Catatan

MAX_ERRORS tidak berlaku untuk CHECK batasan, atau untuk mengonversi jenis data uang dan bigint .

Opsi pemrosesan data MASSAL

FIRSTROW = first_row

Menentukan jumlah baris pertama yang akan dimuat. Defaultnya adalah 1. Ini menunjukkan baris pertama dalam file data yang ditentukan. Jumlah baris ditentukan dengan menghitung terminator baris. FIRSTROW berbasis 1.

LASTROW = last_row

Menentukan jumlah baris terakhir yang akan dimuat. Defaultnya adalah 0. Ini menunjukkan baris terakhir dalam file data yang ditentukan.

ROWS_PER_BATCH = rows_per_batch

Menentukan perkiraan jumlah baris data dalam file data. Nilai ini harus dalam urutan yang sama dengan jumlah baris aktual.

OPENROWSET selalu mengimpor file data sebagai satu batch. Namun, jika Anda menentukan rows_per_batch dengan nilai > 0, prosesor kueri menggunakan nilai rows_per_batch sebagai petunjuk untuk mengalokasikan sumber daya dalam rencana kueri.

Secara default, ROWS_PER_BATCH tidak diketahui. Menentukan ROWS_PER_BATCH = 0 sama dengan menghilangkan ROWS_PER_BATCH.

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

Petunjuk opsional yang menentukan bagaimana data dalam file data diurutkan. Secara default, operasi massal mengasumsikan file data tidak diurutkan. Performa dapat meningkat jika pengoptimal kueri dapat mengeksploitasi urutan untuk menghasilkan rencana kueri yang lebih efisien. Contoh saat menentukan pengurutan dapat bermanfaat termasuk yang berikut ini:

Menyisipkan baris ke dalam tabel yang memiliki indeks berkluster, di mana data himpunan baris diurutkan pada kunci indeks berkluster.
Menggabungkan set baris dengan tabel lain, di mana kolom pengurutan dan gabungan cocok.
Mengagregasi data himpunan baris menurut kolom pengurutan.
Menggunakan set baris sebagai tabel sumber dalam FROM klausa kueri, di mana kolom pengurutan dan gabungan cocok.

UNIQUE

Menentukan bahwa file data tidak memiliki entri duplikat.

Jika baris aktual dalam file data tidak diurutkan sesuai dengan urutan yang ditentukan, atau jika UNIQUE petunjuk ditentukan dan kunci duplikat ada, kesalahan akan ditampilkan.

Alias kolom diperlukan saat ORDER digunakan. Daftar alias kolom harus mereferensikan tabel turunan yang sedang diakses oleh BULK klausa. Nama kolom yang ditentukan dalam ORDER klausul merujuk ke daftar alias kolom ini. Jenis nilai besar (varchar(max), nvarchar(max), varbinary(max), dan xml) dan jenis objek besar (LOB) (teks, ntext, dan gambar) tidak dapat ditentukan.

SINGLE_BLOB

Mengembalikan konten data_file sebagai baris tunggal, kumpulan baris kolom tunggal dari jenis varbinary(maks).

Penting

Kami menyarankan agar Anda mengimpor data XML hanya menggunakan SINGLE_BLOB opsi, bukan SINGLE_CLOB dan SINGLE_NCLOB, karena hanya SINGLE_BLOB mendukung semua konversi pengodean Windows.

SINGLE_CLOB

Dengan membaca data_file sebagai ASCII, mengembalikan konten sebagai baris tunggal, kumpulan baris kolom tunggal dari jenis varchar(maks), menggunakan kolase database saat ini.

SINGLE_NCLOB

Dengan membaca data_file sebagai Unicode, mengembalikan konten sebagai baris tunggal, kumpulan baris kolom tunggal jenis nvarchar(maks), menggunakan kolase database saat ini.

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

Opsi format file input MASSAL

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

Menentukan halaman kode data dalam file data. CODEPAGE hanya relevan jika data berisi kolom karakter, varchar, atau teks dengan nilai karakter lebih dari 127 atau kurang dari 32.

Penting

CODEPAGE bukan opsi yang didukung di Linux.

Catatan

Kami menyarankan agar Anda menentukan nama kolajek untuk setiap kolom dalam file format, kecuali jika Anda ingin opsi 65001 memiliki prioritas atas spesifikasi halaman kolab/kode.

Nilai CODEPAGE	Deskripsi
ACP	Mengonversi kolom tipe data karakter, varchar, atau teks dari halaman kode ANSI/Microsoft Windows (ISO 1252) ke halaman kode SQL Server.
OEM (default)	Mengonversi kolom jenis data karakter, varchar, atau teks dari halaman kode OEM sistem ke halaman kode SQL Server.
RAW	Tidak ada konversi yang terjadi dari satu halaman kode ke halaman lain. Ini adalah opsi tercepat.
code_page	Menunjukkan halaman kode sumber tempat data karakter dalam file data dikodekan; misalnya, 850. Versi Penting sebelum SQL Server 2016 (13.x) tidak mendukung halaman kode 65001 (pengodean UTF-8).

FORMAT = 'CSV'

Dimulai dengan SQL Server 2017 (14.x), argumen ini menentukan file nilai yang dipisahkan koma yang sesuai dengan standar RFC 4180 .

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'

Menentukan jalur lengkap file format. SQL Server mendukung dua jenis file format: XML dan non-XML.

File format diperlukan untuk menentukan jenis kolom dalam tataan hasil. Satu-satunya pengecualian adalah ketika SINGLE_CLOB, SINGLE_BLOB, atau SINGLE_NCLOB ditentukan; dalam hal ini, file format tidak diperlukan.

Untuk informasi tentang format file, lihat Menggunakan file format untuk mengimpor data secara massal (SQL Server).

Dimulai dengan SQL Server 2017 (14.x), format_file_path dapat berada di Azure Blob Storage. Misalnya, lihat Contoh akses massal ke data di Azure Blob Storage.

FIELDQUOTE = 'field_quote'

Dimulai dengan SQL Server 2017 (14.x), argumen ini menentukan karakter yang digunakan sebagai karakter kuotasi dalam file CSV. Jika tidak ditentukan, karakter kuotasi (") digunakan sebagai karakter kutipan seperti yang didefinisikan dalam standar RFC 4180 .

Keterangan

OPENROWSET dapat digunakan untuk mengakses data jarak jauh dari sumber data OLE DB hanya ketika opsi registri DisallowAdhocAccess secara eksplisit diatur ke 0 untuk penyedia yang ditentukan, dan opsi konfigurasi lanjutan Kueri Terdistribusi Ad Hoc diaktifkan. Saat opsi ini tidak diatur, perilaku default tidak memungkinkan akses ad hoc.

Saat Anda mengakses sumber data OLE DB jarak jauh, identitas masuk koneksi tepercaya tidak secara otomatis didelegasikan dari server tempat klien terhubung ke server yang sedang dikueri. Delegasi autentikasi harus dikonfigurasi.

Nama katalog dan skema diperlukan jika penyedia OLE DB mendukung beberapa katalog dan skema di sumber data yang ditentukan. Nilai untuk katalog dan skema dapat dihilangkan ketika penyedia OLE DB tidak mendukungnya. Jika penyedia hanya mendukung nama skema, nama dua bagian dari skema formulir.objek harus ditentukan. Jika penyedia hanya mendukung nama katalog, nama tiga bagian katalog formulir.skema.objek harus ditentukan. Nama tiga bagian harus ditentukan untuk kueri pass-through yang menggunakan penyedia SQL Server Native Client OLE DB. Untuk informasi selengkapnya, lihat Konvensi sintaks t-SQL.

OPENROWSET tidak menerima variabel untuk argumennya.

Setiap panggilan ke OPENDATASOURCE, OPENQUERY, atau OPENROWSET dalam FROM klausul dievaluasi secara terpisah dan independen dari panggilan apa pun ke fungsi-fungsi ini yang digunakan sebagai target pembaruan, bahkan jika argumen identik diberikan ke dua panggilan. Secara khusus, kondisi filter atau gabungan yang diterapkan pada hasil salah satu panggilan tersebut tidak berpengaruh pada hasil yang lain.

Menggunakan OPENROWSET dengan opsi MASSAL

Penyempurnaan Transact-SQL berikut mendukung OPENROWSET(BULK...) fungsi :

FROM Klausa yang digunakan dengan SELECT dapat memanggil OPENROWSET(BULK...) alih-alih nama tabel, dengan fungsionalitas penuhSELECT.

OPENROWSETBULK dengan opsi memerlukan nama korelasi, juga dikenal sebagai variabel rentang atau alias, dalam FROM klausa. Alias kolom dapat ditentukan. Jika daftar alias kolom tidak ditentukan, file format harus memiliki nama kolom. Menentukan alias kolom mengambil alih nama kolom dalam file format, seperti:
- FROM OPENROWSET(BULK...) AS table_alias
- FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
Penting

Kegagalan untuk menambahkan AS <table_alias> akan mengakibatkan kesalahan: Msg 491, Level 16, Status 1, Baris 20 Nama korelasi harus ditentukan untuk set baris massal dalam klausa from.
Pernyataan SELECT...FROM OPENROWSET(BULK...) meminta data dalam file secara langsung, tanpa mengimpor data ke dalam tabel. SELECT...FROM OPENROWSET(BULK...) pernyataan juga dapat mencantumkan alias kolom massal dengan menggunakan file format untuk menentukan nama kolom, dan juga jenis data.
Menggunakan OPENROWSET(BULK...) sebagai tabel sumber dalam INSERT pernyataan atau MERGE mengimpor data secara massal dari file data ke dalam tabel SQL Server. Untuk informasi selengkapnya, lihat Menggunakan BULK INSERT atau OPENROWSET(BULK...) untuk mengimpor data ke SQL Server.
OPENROWSET BULK Saat opsi digunakan dengan INSERT pernyataan, BULK klausa mendukung petunjuk tabel. Selain petunjuk tabel reguler, seperti TABLOCK, BULK klausa dapat menerima petunjuk tabel khusus berikut: IGNORE_CONSTRAINTS (hanya CHECK mengabaikan batasan dan FOREIGN KEY ), IGNORE_TRIGGERS, KEEPDEFAULTS, dan KEEPIDENTITY. Untuk informasi selengkapnya, lihat Petunjuk Tabel (Transact-SQL).

Untuk informasi tentang cara menggunakan INSERT...SELECT * FROM OPENROWSET(BULK...) pernyataan, lihat Impor dan Ekspor Data Massal (SQL Server). Untuk informasi tentang kapan operasi penyisipan baris yang dilakukan oleh impor massal dicatat dalam log transaksi, lihat Prasyarat untuk pengelogan minimal dalam impor massal.

Catatan

Saat Anda menggunakan OPENROWSET, penting untuk memahami bagaimana SQL Server menangani peniruan identitas. Untuk informasi tentang pertimbangan keamanan, lihat Menggunakan BULK INSERT atau OPENROWSET(BULK...) untuk mengimpor data ke SQL Server.

Mengimpor data SQLCHAR, SQLNCHAR, atau SQLBINARY secara massal

OPENROWSET(BULK...) mengasumsikan bahwa, jika tidak ditentukan, panjang SQLCHARmaksimum , SQLNCHAR, atau SQLBINARY data tidak melebihi 8.000 byte. Jika data yang diimpor berada di bidang data LOB yang berisi objek varchar(max), nvarchar(max), atau varbinary(max) yang melebihi 8.000 byte, Anda harus menggunakan file format XML yang menentukan panjang maksimum untuk bidang data. Untuk menentukan panjang maksimum, edit file format dan deklarasikan atribut MAX_LENGTH.

Catatan

File format yang dihasilkan secara otomatis tidak menentukan panjang atau panjang maksimum untuk bidang LOB. Namun, Anda dapat mengedit file format dan menentukan panjang atau panjang maksimum secara manual.

Mengekspor atau mengimpor dokumen SQLXML secara massal

Untuk mengekspor atau mengimpor data SQLXML secara massal, gunakan salah satu jenis data berikut dalam file format Anda.

Jenis data	Efek
`SQLCHAR` atau `SQLVARYCHAR`	Data dikirim di halaman kode klien, atau di halaman kode yang disiratkan oleh kolase.
`SQLNCHAR` atau `SQLNVARCHAR`	Data dikirim sebagai Unicode.
`SQLBINARY` atau `SQLVARYBIN`	Data dikirim tanpa konversi apa pun.

Izin

OPENROWSET izin ditentukan oleh izin nama pengguna yang sedang diteruskan ke penyedia OLE DB. Untuk menggunakan BULK opsi memerlukan ADMINISTER BULK OPERATIONS atau ADMINISTER DATABASE BULK OPERATIONS izin.

Contoh

Bagian ini menyediakan contoh umum untuk menunjukkan cara menggunakan OPENROWSET.

J. Menggunakan OPENROWSET dengan SELECT dan Penyedia SQL Server Native Client OLE DB

Berlaku untuk: SQL Server saja.

SQL Server Native Client (sering disingkat SNAC) telah dihapus dari SQL Server 2022 (16.x) dan SQL Server Management Studio 19 (SSMS). Penyedia SQL Server Native Client OLE DB (SQLNCLI atau SQLNCLI11) dan Penyedia Microsoft OLE DB warisan untuk SQL Server (SQLOLEDB) tidak direkomendasikan untuk pengembangan baru. Beralih ke Microsoft OLE DB Driver (MSOLEDBSQL) baru untuk SQL Server ke depannya.

Contoh berikut menggunakan penyedia SQL Server Native Client OLE DB untuk mengakses HumanResources.Department tabel dalam AdventureWorks2022 database di server Seattle1jarak jauh . (Gunakan SQLNCLI dan SQL Server akan mengalihkan ke versi terbaru Penyedia SQL Server Native Client OLE DB.) Pernyataan SELECT digunakan untuk menentukan kumpulan baris yang dikembalikan. String penyedia berisi Server kata kunci dan Trusted_Connection . Kata kunci ini dikenali oleh penyedia SQL Server Native Client OLE DB.

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

B. Menggunakan Penyedia Microsoft OLE DB untuk Jet

Berlaku untuk: SQL Server saja.

Contoh berikut mengakses Customers tabel dalam database Microsoft Access Northwind melalui Penyedia Microsoft OLE DB untuk Jet.

Catatan

Contoh ini mengasumsikan bahwa Microsoft Access diinstal. Untuk menjalankan contoh ini, Anda harus menginstal Northwind database.

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

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

C. Menggunakan OPENROWSET dan tabel lain dalam INNER JOIN

Berlaku untuk: SQL Server saja.

Contoh berikut memilih semua data dari Customers tabel dari instans lokal database SQL Server Northwind dan dari Orders tabel dari database Access Northwind yang disimpan di komputer yang sama.

Catatan

Contoh ini mengasumsikan bahwa Access diinstal. Untuk menjalankan contoh ini, Anda harus menginstal Northwind database.

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;

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

D. Menggunakan OPENROWSET untuk MENYISIPKAN data file SECARA MASSAL ke dalam kolom varbinary(maks)

Berlaku untuk: SQL Server saja.

Contoh berikut membuat tabel kecil untuk tujuan demonstrasi, dan menyisipkan data file dari file bernama Text1.txt yang terletak di C: direktori akar ke dalam kolom varbinary(max).

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

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

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

E. Gunakan penyedia OPENROWSET BULK dengan file format untuk mengambil baris dari file teks

Berlaku untuk: SQL Server saja.

Contoh berikut menggunakan file format untuk mengambil baris dari file teks yang dibatasi tab, values.txt yang berisi data berikut:

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

File format, values.fmt, menjelaskan kolom di 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

Ini adalah kueri yang mengambil data tersebut:

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

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

F. Tentukan file format dan halaman kode

Berlaku untuk: SQL Server saja.

Contoh berikut menunjukkan cara menggunakan opsi file format dan halaman kode secara bersamaan.

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

G. Mengakses data dari file CSV dengan file format

Berlaku untuk: SQL Server 2017 (14.x) dan versi yang lebih baru saja.

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

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

H. Mengakses data dari file CSV tanpa file format

Berlaku untuk: SQL Server saja.

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

Penting

Driver ODBC harus 64-bit. Buka tab Driver dari Koneksi ke aplikasi Sumber Data ODBC (Wizard Impor dan Ekspor SQL Server) di Windows untuk memverifikasi ini. Ada 32-bit Microsoft Text Driver (*.txt, *.csv) yang tidak akan berfungsi dengan versi 64-bit .sqlservr.exe

I. Mengakses data dari file yang disimpan di Azure Blob Storage

Berlaku untuk: SQL Server 2017 (14.x) dan versi yang lebih baru saja.

Dimulai dengan SQL Server 2017 (14.x), contoh berikut menggunakan sumber data eksternal yang menunjuk ke kontainer di akun penyimpanan Azure dan kredensial lingkup database yang dibuat untuk tanda tangan akses bersama.

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

Untuk contoh lengkap OPENROWSET termasuk mengonfigurasi kredensial dan sumber data eksternal, lihat Contoh akses massal ke data di Azure Blob Storage.

j. Mengimpor ke dalam tabel dari file yang disimpan di Azure Blob Storage

Contoh berikut menunjukkan cara menggunakan OPENROWSET perintah untuk memuat data dari file csv di lokasi penyimpanan Azure Blob tempat Anda membuat kunci SAS. Lokasi penyimpanan Azure Blob dikonfigurasi sebagai sumber data eksternal. Ini memerlukan kredensial lingkup database menggunakan tanda tangan akses bersama yang dienkripsi menggunakan kunci master dalam database pengguna.

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

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

K. Menggunakan identitas terkelola untuk sumber eksternal

Contoh berikut membuat kredensial dengan menggunakan identitas terkelola, membuat sumber eksternal lalu memuat data dari CSV yang dihosting di sumber eksternal.

Pertama, buat kredensial dan tentukan penyimpanan blob sebagai sumber eksternal:

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

Selanjutnya, muat data dari file CSV yang dihosting pada penyimpanan blob:

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

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

L. Menggunakan OPENROWSET untuk mengakses beberapa file parket menggunakan penyimpanan objek yang kompatibel dengan S3

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru.

Contoh berikut menggunakan akses beberapa file parket dari lokasi yang berbeda, semuanya disimpan di penyimpanan objek yang kompatibel dengan S3:

CREATE DATABASE SCOPED CREDENTIAL s3_dsc
WITH IDENTITY = 'S3 Access Key',
SECRET = 'contosoadmin:contosopwd';
GO

CREATE EXTERNAL DATA SOURCE s3_eds
WITH
(
    LOCATION = 's3://10.199.40.235:9000/movies',
    CREDENTIAL = s3_dsc
);
GO

SELECT * FROM OPENROWSET(
    BULK (
        '/decades/1950s/*.parquet',
        '/decades/1960s/*.parquet',
        '/decades/1970s/*.parquet'
    ),
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_eds'
) AS data;

M. Menggunakan OPENROWSET untuk mengakses beberapa file delta dari Azure Data Lake Gen2

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru.

Dalam contoh ini, kontainer tabel data diberi nama Contoso, dan terletak di akun penyimpanan Azure Data Lake Gen2.

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

SELECT *
FROM OPENROWSET(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS result;

Contoh lainnya

Untuk contoh selengkapnya yang memperlihatkan penggunaan INSERT...SELECT * FROM OPENROWSET(BULK...), lihat artikel berikut ini:

OPENROWSET (T-SQL)

Sintaksis

Argumen

Argumen umum

'provider_name'

'sumber data'

'user_id'

'kata sandi'

'provider_string'

<table_or_view>

'kueri'

Argumen MASSAL

'data_file' MASSAL

Opsi penanganan kesalahan MASSAL

ERRORFILE ='file_name'

ERRORFILE_DATA_SOURCE_NAME

MAXERRORS = maximum_errors

Opsi pemrosesan data MASSAL

FIRSTROW = first_row

LASTROW = last_row

ROWS_PER_BATCH = rows_per_batch

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

UNIQUE

SINGLE_BLOB

SINGLE_CLOB

SINGLE_NCLOB

Opsi format file input MASSAL

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

FORMAT = 'CSV'

FORMATFILE = 'format_file_path'

FIELDQUOTE = 'field_quote'

Keterangan

Menggunakan OPENROWSET dengan opsi MASSAL

Mengimpor data SQLCHAR, SQLNCHAR, atau SQLBINARY secara massal

Mengekspor atau mengimpor dokumen SQLXML secara massal

Izin

Contoh

J. Menggunakan OPENROWSET dengan SELECT dan Penyedia SQL Server Native Client OLE DB

B. Menggunakan Penyedia Microsoft OLE DB untuk Jet

C. Menggunakan OPENROWSET dan tabel lain dalam INNER JOIN

D. Menggunakan OPENROWSET untuk MENYISIPKAN data file SECARA MASSAL ke dalam kolom varbinary(maks)

E. Gunakan penyedia OPENROWSET BULK dengan file format untuk mengambil baris dari file teks

F. Tentukan file format dan halaman kode

G. Mengakses data dari file CSV dengan file format

H. Mengakses data dari file CSV tanpa file format

I. Mengakses data dari file yang disimpan di Azure Blob Storage

j. Mengimpor ke dalam tabel dari file yang disimpan di Azure Blob Storage

K. Menggunakan identitas terkelola untuk sumber eksternal

L. Menggunakan OPENROWSET untuk mengakses beberapa file parket menggunakan penyimpanan objek yang kompatibel dengan S3

M. Menggunakan OPENROWSET untuk mengakses beberapa file delta dari Azure Data Lake Gen2

Contoh lainnya

Konten terkait

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan: