OPENROWSET (SQL Bertransaksi)

Artikel
07/20/2022
18 menit untuk membaca

Berlaku untuk: SQL Server (semua versi yang didukung) 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 klausul FROM kueri seolah-olah itu adalah nama tabel. Fungsi ini OPENROWSET juga dapat direferensikan sebagai tabel target dari INSERTpernyataan , UPDATE, atau DELETE , tunduk pada kemampuan penyedia OLE DB. Meskipun kueri mungkin mengembalikan beberapa tataan hasil, OPENROWSET hanya mengembalikan yang pertama.

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

Topic link icon Konvensi Sintaksis T-SQL

Sintaks

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 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

'provider_name'

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

Adalah konstanta string yang sesuai dengan sumber data OLE DB tertentu. sumber data adalah properti DBPROP_INIT_DATASOURCE yang akan diteruskan ke antarmuka IDBProperties penyedia untuk menginisialisasi penyedia. Biasanya, string ini menyertakan nama file database, nama server database, atau nama yang dipahami penyedia untuk menemukan database atau database. Sumber data dapat menjadi jalur C:\SAMPLES\Northwind.mdb' file untuk Microsoft.Jet.OLEDB.4.0 penyedia, atau string Server=Seattle1;Trusted_Connection=yes; koneksi untuk SQLNCLI penyedia.

'user_id'

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

'kata sandi'

Adalah konstanta string yang merupakan kata sandi pengguna yang akan diteruskan ke penyedia OLE DB. kata sandi diteruskan sebagai properti DBPROP_AUTH_PASSWORD saat menginisialisasi penyedia. kata sandi tidak boleh 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'

Adalah string koneksi khusus penyedia yang diteruskan sebagai properti DBPROP_INIT_PROVIDERSTRING untuk menginisialisasi penyedia OLE DB. provider_string biasanya merangkum semua informasi koneksi yang diperlukan untuk menginisialisasi penyedia. Untuk daftar kata kunci yang dikenali oleh penyedia SQL Server Native Client OLE DB, lihat Properti Inisialisasi dan Otorisasi.

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.
object - nama objek yang secara unik mengidentifikasi objek untuk dikerjakan.

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

'kueri'

Adalah konstanta string yang 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 Referensi SQL Server Native Client (OLE DB).

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

MASSAL

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

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

Argumen opsi MASSAL memungkinkan kontrol 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, kumpulan baris kolom tunggal dari jenis varbinary, varchar, atau nvarchar. Perilaku default dijelaskan dalam deskripsi argumen yang mengikuti.

Untuk informasi tentang cara menggunakan opsi MASSAL, lihat "Komentar," nanti dalam topik ini. Untuk informasi tentang izin yang diperlukan oleh opsi MASSAL, lihat "Izin," nanti dalam topik ini.

Catatan

Ketika 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 (SQL Server).

'data_file' MASSAL

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

Berlaku untuk: SQL Server 2017 (14.x) CTP 1.1. Dimulai dengan CTP 1.1 SQL Server 2017 (14.x), data_file dapat berada di penyimpanan blob Azure. 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

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 akan muncul 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. Berlaku untuk: SQL Server 2017 (14.x) CTP 1.1. Dimulai dengan SQL Server 2017 (14.x), error_file_path dapat berada di penyimpanan blob Azure.

ERRORFILE_DATA_SOURCE_NAME

Berlaku untuk: SQL Server 2017 (14.x) CTP 1.1. 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 opsi yang TYPE = BLOB_STORAGE ditambahkan di SQL Server 2017 (14.x) CTP 1.1. Untuk informasi selengkapnya, lihat MEMBUAT SUMBER DATA EKSTERNAL.

MAXERRORS

MAXERRORS =maximum_errors menentukan jumlah maksimum kesalahan sintaks atau baris yang tidak sesuai, seperti yang didefinisikan dalam file format, yang dapat terjadi sebelum OPENROWSET memberikan 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 batasan CHECK, atau untuk mengonversi jenis data uang dan bigint .

Opsi pemrosesan Data MASSAL

FIRSTROW

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

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 =rows_per_batch Menentukan perkiraan jumlah baris data dalam file data. Nilai ini harus memiliki 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

ORDER ( { column [ ASC | DESC ] } [ ,... n ] [ UNIQUE ] ) Petunjuk opsional yang menentukan bagaimana data dalam file data diurutkan. Secara default, operasi massal mengasumsikan file data tidak diurutkan. Performa mungkin meningkat jika urutan yang ditentukan dapat dieksploitasi oleh pengoptimal kueri 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 kumpulan baris dengan tabel lain, di mana kolom pengurutan dan gabungan cocok.
Menggabungkan data himpunan baris menurut kolom pengurutan.
Menggunakan kumpulan baris sebagai tabel sumber dalam klausa FROM kueri, di mana kolom pengurutan dan gabungan cocok.

UNIQUE

UNIQUE menentukan bahwa file data tidak memiliki entri duplikat.

Jika baris aktual dalam file data tidak diurutkan sesuai dengan urutan yang ditentukan, atau jika petunjuk UNIQUE 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 klausa MASSAL. Nama kolom yang ditentukan dalam klausa ORDER 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 opsi SINGLE_BLOB, bukan SINGLE_CLOB dan SINGLE_NCLOB, karena hanya SINGLE_BLOB yang mendukung semua konversi pengodean Windows.

SINGLE_CLOB

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

SINGLE_NCLOB

Dengan membaca data_file sebagai UNICODE, mengembalikan konten sebagai kumpulan baris tunggal 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

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 kolase untuk setiap kolom dalam file format, kecuali jika Anda ingin opsi 65001 memiliki prioritas atas spesifikasi halaman kolase/kode.

Nilai CODEPAGE	Deskripsi
ACP	Mengonversi kolom tipe data karakter, varchar, atau teks dari halaman kode Windows ANSI/Microsoft (ISO 1252) ke halaman kode SQL Server.
OEM (default)	Mengonversi kolom tipe 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 lainnya. Ini adalah opsi tercepat.
code_page	Menunjukkan halaman kode sumber tempat data karakter dalam file data dikodekan; misalnya, 850. Penting Versi sebelum SQL Server 2016 (13.x) tidak mendukung halaman kode 65001 (pengodean UTF-8).

FORMAT

FORMAT='CSV' Berlaku untuk: SQL Server 2017 (14.x) CTP 1.1. 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

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

Berlaku untuk: SQL Server 2017 (14.x) CTP 1.1. Dimulai dengan SQL Server 2017 (14.x) CTP 1.1, format_file_path dapat berada di penyimpanan blob Azure. Misalnya, lihat Contoh Akses Massal ke Data di Azure Blob Storage.

FIELDQUOTE

FIELDQUOTE='field_quote' Berlaku untuk: SQL Server 2017 (14.x) CTP 1.1. Menentukan karakter yang akan digunakan sebagai karakter kuotasi dalam file CSV. Jika tidak ditentukan, karakter kuotasi (") akan digunakan sebagai karakter kuotasi 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. Ketika opsi ini tidak diatur, perilaku default tidak memungkinkan akses ad hoc.

Saat 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 transact-SQL (Transact-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

Peningkatan SQL Transact berikut mendukung fungsi OPENROWSET(BULK...):

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

OPENROWSETBULK dengan opsi memerlukan nama korelasi, juga dikenal sebagai variabel rentang atau alias, dalam FROM klausul . Alias kolom dapat ditentukan. Jika daftar alias kolom tidak ditentukan, file format harus memiliki nama kolom. Menentukan alias kolom akan menggantikan 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, Tingkat 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 Mengimpor Data Massal dengan Menggunakan BULK INSERT atau OPENROWSET(BULK...) (SQL Server).
OPENROWSET BULK Saat opsi digunakan dengan INSERT pernyataan, klausa BULK 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, , KEEPDEFAULTSdan 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. Untuk informasi tentang pertimbangan keamanan, lihat Mengimpor Data Massal dengan Menggunakan INSERT MASSAL atau OPENROWSET(BULK...) (SQL Server).

Mengimpor Data SQLCHAR, SQLNCHAR, atau SQLBINARY Secara Massal

OPENROWSET(BULK...) mengasumsikan bahwa, jika tidak ditentukan, panjang maksimum data SQLCHAR, SQLNCHAR, atau SQLBINARY tidak melebihi 8000 byte. Jika data yang diimpor berada di bidang data LOB yang berisi objek varchar(max), nvarchar(max), atau varbinary(max) yang melebihi 8000 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 diteruskan ke penyedia OLE DB. Untuk menggunakan opsi ini BULK diperlukan ADMINISTER BULK OPERATIONS atau ADMINISTER DATABASE BULK OPERATIONS izin.

Contoh

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

Contoh berikut menggunakan penyedia SQL Server Native Client OLE DB untuk mengakses HumanResources.Department tabel dalam database AdventureWorks2012 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 AdventureWorks2012.HumanResources.Department
      ORDER BY GroupName, Name') AS a;

B. Menggunakan Penyedia Microsoft OLE DB untuk Jet

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

Catatan

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

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

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 database Northwind.

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(max)

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 varbinary(max) kolom.

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

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

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

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

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. Menentukan file format dan halaman kode

Contoh berikut menunjukkan cara menggunakan opsi halaman format file dan 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) CTP 1.1.

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

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 aplikasi Sumber Data OBDC di Windows untuk memverifikasi ini. Ada 32-bit Microsoft Text Driver (*.txt, *.csv) yang tidak akan berfungsi dengan versi 64-bit sqlservr.exe.
Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

i. Mengakses data dari file yang disimpan di penyimpanan Azure Blob

Berlaku untuk: SQL Server 2017 (14.x) CTP 1.1. 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 penyimpanan Azure Blob

Contoh berikut menunjukkan cara menggunakan perintah OPENROWSET 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***************';

 -- NOTE: Make sure that you don't have a leading ? in 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= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

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 menggunakan token SAS.

Contoh tambahan

Untuk contoh tambahan yang memperlihatkan penggunaan INSERT...SELECT * FROM OPENROWSET(BULK...), lihat topik berikut:

OPENROWSET (SQL Bertransaksi)

Sintaks

Argumen

'provider_name'

'sumber data'

'user_id'

'kata sandi'

'provider_string'

<table_or_view>

'kueri'

MASSAL

'data_file' MASSAL

Opsi penanganan Kesalahan MASSAL

ERRORFILE

ERRORFILE_DATA_SOURCE_NAME

MAXERRORS

Opsi pemrosesan Data MASSAL

FIRSTROW

LASTROW

ROWS_PER_BATCH

ORDER

UNIQUE

SINGLE_BLOB

SINGLE_CLOB

SINGLE_NCLOB

Opsi format file Input MASSAL

CODEPAGE

FORMAT

FORMATFILE

FIELDQUOTE

Keterangan

Menggunakan OPENROWSET dengan Opsi MASSAL

Mengimpor Data SQLCHAR, SQLNCHAR, atau SQLBINARY Secara Massal

Mengekspor atau Mengimpor Dokumen SQLXML Secara Massal

Izin

Contoh

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

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(max)

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

F. Menentukan 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 penyimpanan Azure Blob

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

Contoh tambahan

Lihat juga