OPENROWSET MASSAL (Transact-SQL)

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceTitik akhir analitik SQL di Microsoft FabricGudang di Microsoft FabricDatabase SQL di Microsoft Fabric

Fungsi membaca OPENROWSET data dari satu atau banyak file dan mengembalikan konten sebagai set baris. Bergantung pada layanan, file mungkin disimpan di Azure Blob Storage, penyimpanan Azure Data Lake, disk lokal, berbagi jaringan, dll. Anda dapat membaca berbagai format file seperti teks/CSV, Parquet, atau baris JSON.

Fungsi OPENROWSET dapat dirujuk dalam FROM klausa kueri seolah-olah itu adalah nama tabel. Ini dapat digunakan untuk membaca data dalam SELECT pernyataan, atau untuk memperbarui data target dalam UPDATEpernyataan , , INSERT, DELETEMERGE, CTAS, atau CETAS .

• OPENROWSET(BULK) dirancang untuk membaca data dari file data eksternal.
• OPENROWSET tanpa BULK dirancang untuk membaca dari mesin database lain. Untuk informasi selengkapnya, lihat OPENROWSET (Transact-SQL).

Artikel ini dan argumen yang OPENROWSET(BULK) ditetapkan bervariasi di antara platform.

• Untuk sintaks Microsoft Fabric, pilih Fabric di daftar dropdown versi.
• Untuk sintaks SQL Server, Azure SQL Database, dan Azure SQL Managed Instance, pilih platform Anda di daftar dropdown versi.

Detail dan tautan ke contoh serupa di platform lain:

• Untuk informasi selengkapnya tentang OPENROWSET di Azure SQL Database, lihat Virtualisasi data dengan Azure SQL Database.
• Untuk informasi selengkapnya tentang OPENROWSET di Azure SQL Managed Instance, lihat Virtualisasi data dengan Azure SQL Managed Instance.
• 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

Syntax

Untuk SQL Server, Azure SQL Database, SQL database di Fabric, dan Azure SQL Managed Instance:

OPENROWSET( BULK 'data_file_path',
            <bulk_option> ( , <bulk_option> )*
)
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]

<bulk_option> ::=
   DATA_SOURCE = 'data_source_name' |

   -- file format options
   CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } |
   DATAFILETYPE = { 'char' | 'widechar' } |
   FORMAT = <file_format> |

   FORMATFILE = 'format_file_path' |
   FORMATFILE_DATA_SOURCE = 'data_source_name' |

   SINGLE_BLOB |
   SINGLE_CLOB |
   SINGLE_NCLOB |

   -- Text/CSV options
   ROWTERMINATOR = 'row_terminator' |
   FIELDTERMINATOR =  'field_terminator' |
   FIELDQUOTE = 'quote_character' |

   -- Error handling options
   MAXERRORS = maximum_errors |
   ERRORFILE = 'file_name' |
   ERRORFILE_DATA_SOURCE = 'data_source_name' |

   -- Execution options
   FIRSTROW = first_row |
   LASTROW = last_row |

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

   ROWS_PER_BATCH = rows_per_batch

Arguments

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.

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

'data_file_path' MASSAL

Jalur atau URI file data yang datanya akan dibaca dan dikembalikan sebagai kumpulan baris.

URI dapat mereferensikan penyimpanan Azure Data Lake atau penyimpanan Azure Blob. URI file data yang datanya akan dibaca dan dikembalikan sebagai kumpulan baris.

Format jalur yang didukung adalah:

• <drive letter>:\<file path> untuk mengakses file pada disk lokal
• \\<network-share\<file path> untuk mengakses file pada berbagi jaringan
• adls://<container>@<storage>.dfs.core.windows.net/<file path> untuk mengakses Azure Data Lake Storage
• abs://<storage>.blob.core.windows.net/<container>/<file path> untuk mengakses Azure Blob Storage
• s3://<ip-address>:<port>/<file path> untuk mengakses penyimpanan yang kompatibel dengan s3

Note

Artikel ini dan pola URI yang didukung berbeda pada platform yang berbeda. Untuk pola URI yang tersedia di Microsoft Fabric Data Warehouse, pilih Fabric di daftar dropdown versi.

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.

URI dapat menyertakan * karakter untuk mencocokkan urutan karakter apa pun, memungkinkan pencocokan OPENROWSET pola terhadap URI. Selain itu, ini dapat diakhir dengan /** untuk mengaktifkan traversal rekursif melalui semua subfolder. Di SQL Server, perilaku ini tersedia dimulai dengan SQL Server 2022 (16.x).

Contohnya:

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<scheme:>//pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/*.parquet'
);

Jenis penyimpanan yang dapat dirujuk oleh URI diperlihatkan dalam tabel berikut:

Versi	On-premises	Penyimpanan Azure	OneLake dalam Kain	S3	Google Cloud (GCS)
SQL Server 2017 (14.x), SQL Server 2019 (15.x)	Yes	Yes	Tidak.	Tidak.	Tidak.
SQL Server 2022 (16.x)	Yes	Yes	Tidak.	Yes	Tidak.
Azure SQL Database	Tidak.	Yes	Tidak.	Tidak.	Tidak.
Azure SQL Managed Instance	Tidak.	Yes	Tidak.	Tidak.	Tidak.
Kumpulan SQL tanpa server di Azure Synapse Analytics	Tidak.	Yes	Yes	Tidak.	Tidak.
Titik akhir analitik Microsoft Fabric Warehouse dan SQL	Tidak.	Yes	Yes	Ya, menggunakan OneLake di pintasan Fabric	Ya, menggunakan OneLake di pintasan Fabric
Database SQL di Microsoft Fabric	Tidak.	Ya, menggunakan OneLake di pintasan Fabric	Yes	Ya, menggunakan OneLake di pintasan Fabric	Ya, menggunakan OneLake di pintasan Fabric

DATA_SOURCE

DATA_SOURCE menentukan lokasi akar jalur file data. Ini memungkinkan Anda menggunakan jalur relatif di jalur MASSAL. Sumber data dibuat dengan CREATE EXTERNAL DATA SOURCE.

Selain lokasi akar, ini dapat menentukan kredensial kustom yang dapat digunakan untuk mengakses file di lokasi tersebut.

Contohnya:

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<scheme:>//pandemicdatalake.blob.core.windows.net/public')
GO
SELECT *
FROM OPENROWSET(
    BULK '/curated/covid-19/bing_covid-19_data/latest/*.parquet',
    DATA_SOURCE = 'root'
);

Opsi format file

CODEPAGE

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. Nilai yang valid adalah 'ACP', 'OEM', 'RAW' atau 'code_page':

Nilai CODEPAGE	Description
ACP	Mengonversi kolom tipe data karakter, varchar, atau teks dari halaman kode ANSI/Microsoft Windows (ISO 1252) ke halaman kode SQL Server.
OEM (standar)	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.

Important

Versi sebelum SQL Server 2016 (13.x) tidak mendukung halaman kode 65001 (pengodean UTF-8). CODEPAGE bukan opsi yang didukung di Linux.

Note

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.

DATAFILETYPE

Menentukan bahwa OPENROWSET(BULK) harus membaca konten file byte tunggal (ASCII, UTF8) atau multi-byte (UTF16). Nilai yang valid adalah karakter dan widechar:

Nilai DATAFILETYPE	Semua data yang diwakili dalam:
karakter (default)	Format karakter. Untuk informasi selengkapnya, lihat Menggunakan Format Karakter untuk Mengimpor atau Mengekspor Data.
widechar	Karakter Unicode. Untuk informasi selengkapnya, lihat Menggunakan Format Karakter Unicode untuk Mengimpor atau Mengekspor Data.

FORMAT

Menentukan format file yang dirujuk, misalnya:

SELECT *
FROM OPENROWSET(BULK N'<data-file-path>',
                FORMAT='CSV') AS cars;

Nilai yang valid adalah 'CSV' (file nilai yang dipisahkan koma sesuai dengan standar RFC 4180 ), 'PARQUET', 'DELTA' (versi 1.0), dan 'JSONL', tergantung pada versi:

Versi	CSV	PARKET	DELTA	JSONL
SQL Server 2017 (14.x), SQL Server 2019 (15.x)	Yes	Tidak.	Tidak.	Tidak.
SQL Server 2022 (16.x) dan versi yang lebih baru	Yes	Yes	Yes	Tidak.
Azure SQL Database	Yes	Yes	Yes	Tidak.
Azure SQL Managed Instance	Yes	Yes	Yes	Tidak.
Kumpulan SQL tanpa server di Azure Synapse Analytics	Yes	Yes	Yes	Tidak.
Titik akhir analitik Microsoft Fabric Warehouse dan SQL	Yes	Yes	Tidak.	Yes
Database SQL di Microsoft Fabric	Yes	Yes	Tidak.	Tidak.

FORMATFILE

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

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'D:\XChange\test-csv.csv',
      FORMATFILE= 'D:\XChange\test-format-file.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.

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE menentukan lokasi akar jalur file format. Ini memungkinkan Anda untuk menggunakan jalur relatif dalam opsi FORMATFILE.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '//pandemicdatalake/public/curated')
GO
SELECT *
FROM OPENROWSET(
    BULK '//pandemicdatalake/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
    FORMATFILE = 'covid-19/bing_covid-19_data/latest/bing_covid-19_data.fmt',
    FORMATFILE_DATA_SOURCE = 'root'
);

Sumber data file format dibuat dengan CREATE EXTERNAL DATA SOURCE. Selain lokasi akar, ini dapat menentukan kredensial kustom yang dapat digunakan untuk mengakses file di lokasi tersebut.

Opsi Teks/CSV

ROWTERMINATOR

Menentukan terminator baris yang akan digunakan untuk file data karakter dan widechar , misalnya:

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWTERMINATOR = '\n'
);

Terminator baris default adalah \r\n (karakter baris baru). Untuk informasi selengkapnya, lihat Menentukan terminator bidang dan baris.

FIELDTERMINATOR

Menentukan terminator bidang yang akan digunakan untuk file data karakter dan widechar , misalnya:

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDTERMINATOR = '\t'
);

Terminator bidang default adalah , (koma). Untuk informasi selengkapnya, lihat Tentukan Terminator Bidang dan Baris. Misalnya, untuk membaca data yang dibatasi tab dari file:

FIELDQUOTE = 'field_quote'

Dimulai dengan SQL Server 2017 (14.x), argumen ini menentukan karakter yang digunakan sebagai karakter kutipan dalam file CSV, seperti dalam contoh New York berikut:

Empire State Building,40.748817,-73.985428,"20 W 34th St, New York, NY 10118","\icons\sol.png"
Statue of Liberty,40.689247,-74.044502,"Liberty Island, New York, NY 10004","\icons\sol.png"

Hanya satu karakter yang dapat ditentukan sebagai nilai untuk opsi ini. Jika tidak ditentukan, karakter kuotasi (") digunakan sebagai karakter kutipan seperti yang didefinisikan dalam standar RFC 4180 . Karakter FIELDTERMINATOR (misalnya, koma) dapat ditempatkan dalam tanda kutip bidang dan akan dianggap sebagai karakter biasa dalam sel yang dibungkus dengan FIELDQUOTE karakter.

Misalnya, untuk membaca himpunan data CSV sampel New York sebelumnya, gunakan FIELDQUOTE = '"'. Nilai bidang alamat akan dipertahankan sebagai nilai tunggal, tidak dibagi menjadi beberapa nilai dengan koma dalam " karakter (kutipan).

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDQUOTE = '"'
);

Opsi penanganan kesalahan

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

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<error-file-path>'
);

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

Dimulai dengan SQL Server 2017 (14.x), argumen ini adalah sumber data eksternal bernama yang menunjuk ke lokasi file kesalahan yang akan berisi kesalahan yang ditemukan selama impor.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<root-error-file-path>')
GO
SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<relative-error-file-path>',
    ERRORFILE_DATA_SOURCE = 'root'
);

Untuk informasi selengkapnya, lihat CREATE EXTERNAL DATA SOURCE (Transact-SQL).

MAXERROR = 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.

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    MAXERRORS = 0
);

Default untuk maximum_errors adalah 10.

Note

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

Opsi pemrosesan data

BARIS PERTAMA = 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 adalah perkiraan, dan harus berupa perkiraan (dalam satu urutan besaran) dari jumlah baris aktual. Secara default, ROWS_PER_BATCH diperkirakan berdasarkan karakteristik file (jumlah file, ukuran file, ukuran jenis data yang dikembalikan). Menentukan ROWS_PER_BATCH = 0 sama dengan menghilangkan ROWS_PER_BATCH. Contohnya:

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWS_PER_BATCH = 100000
);

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. Daftar berikut ini menyediakan contoh saat menentukan pengurutan bisa bermanfaat:

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

Opsi konten

SINGLE_BLOB

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

Important

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;

Permissions

OPENROWSET dengan sumber data eksternal, memerlukan izin berikut:

• ADMINISTER DATABASE BULK OPERATIONS atau
• ADMINISTER BULK OPERATIONS

Contoh T-SQL berikut memberikan ADMINISTER DATABASE BULK OPERATIONS kepada prinsipal.

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [<principal_name>];

Jika akun penyimpanan target bersifat privat, prinsipal juga harus memiliki peran Pembaca Data Blob Penyimpanan (atau lebih tinggi) yang ditetapkan di tingkat akun kontainer atau penyimpanan.

Remarks

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

• OPENROWSET BULK dengan opsi memerlukan nama korelasi, juga dikenal sebagai variabel rentang atau alias, dalam FROM klausa. Kegagalan untuk menambahkan hasil AS <table_alias> dalam kesalahan Msg 491: "Nama korelasi harus ditentukan untuk set baris massal dalam klausa from."

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

• Pernyataan SELECT...FROM OPENROWSET(BULK...) meminta data dalam file secara langsung, tanpa mengimpor data ke dalam tabel.

• Pernyataan SELECT...FROM OPENROWSET(BULK...) 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. 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 pencatatan minimal dalam impor massal.
• Saat digunakan untuk mengimpor data dengan model pemulihan penuh, OPENROWSET (BULK ...) tidak mengoptimalkan pengelogan.

Note

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.

Note

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

Fungsi metadata file

Terkadang, Anda mungkin perlu mengetahui sumber file atau folder mana yang berkorelasi dengan baris tertentu dalam kumpulan hasil.

Anda dapat menggunakan fungsi filepath dan filename untuk mengembalikan nama file dan/atau jalur dalam kumpulan hasil. Atau Anda dapat menggunakannya untuk memfilter data berdasarkan nama file dan/atau jalur folder. Di bagian berikut, Anda akan menemukan deskripsi singkat di sepanjang sampel.

Fungsi nama file

Fungsi ini mengembalikan nama file dari mana baris berasal.

Jenis data yang dikembalikan adalah nvarchar(1024). Untuk performa yang optimal, selalu transmisikan hasil fungsi nama file ke jenis data yang sesuai. Jika Anda menggunakan tipe data karakter, pastikan panjang yang sesuai digunakan.

Contoh berikut membaca file data NYC Yellow Taxi selama tiga bulan terakhir tahun 2017 dan mengembalikan jumlah perjalanan per file. Bagian OPENROWSET kueri menentukan file mana yang akan dibaca.

SELECT
    nyc.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM  
    OPENROWSET(
        BULK 'parquet/taxi/year=2017/month=9/*.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) nyc
GROUP BY nyc.filename();

Contoh berikut menunjukkan bagaimana filename() dapat digunakan dalam WHERE klausa untuk memfilter file yang akan dibaca. Ini mengakses seluruh folder di OPENROWSET bagian kueri dan memfilter file dalam WHERE klausa.

Hasil Anda akan sama dengan contoh sebelumnya.

SELECT
    r.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2) 
        WITH (C1 varchar(200) ) AS [r]
WHERE
    r.filename() IN ('yellow_tripdata_2017-10.csv', 'yellow_tripdata_2017-11.csv', 'yellow_tripdata_2017-12.csv')
GROUP BY
    r.filename()
ORDER BY
    [filename];

Fungsi filepath

Fungsi ini mengembalikan jalur penuh atau bagian dari jalur:

• Saat dipanggil tanpa parameter, mengembalikan jalur file lengkap dari mana baris berasal.
• Saat dipanggil dengan parameter, mengembalikan bagian jalur yang cocok dengan karakter pengganti pada posisi yang ditentukan dalam parameter. Misalnya, nilai parameter 1 akan mengembalikan bagian jalur yang cocok dengan karakter pengganti pertama.

Jenis data yang dikembalikan adalah nvarchar(1024). Untuk performa yang optimal, selalu transmisikan hasil filepath fungsi ke jenis data yang sesuai. Jika Anda menggunakan tipe data karakter, pastikan panjang yang sesuai digunakan.

Contoh berikut membaca file data NYC Yellow Taxi selama tiga bulan terakhir tahun 2017. Ini mengembalikan jumlah perjalanan per jalur file. Bagian OPENROWSET kueri menentukan file mana yang akan dibaca.

SELECT
    r.filepath() AS filepath
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-1*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
    WITH (
        vendor_id INT
    ) AS [r]
GROUP BY
    r.filepath()
ORDER BY
    filepath;

Contoh berikut menunjukkan bagaimana filepath() dapat digunakan dalam WHERE klausa untuk memfilter file yang akan dibaca.

Anda dapat menggunakan karakter pengganti di OPENROWSET bagian kueri dan memfilter file dalam WHERE klausa. Hasil Anda akan sama dengan contoh sebelumnya.

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
WITH (
    vendor_id INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Examples

Bagian ini menyediakan contoh umum untuk menunjukkan cara menggunakan OPENROWSET BULK sintaksis.

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

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

Kueri ini mengambil data tersebut:

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

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

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

E. Mengakses data dari file CSV tanpa file format

Berlaku untuk: SQL Server saja.

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14\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'
);

Important

Driver ODBC harus 64-bit. Buka tab Driver dari aplikasi Sambungkan ke 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

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

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

Di SQL Server 2017 (14.x) dan versi yang lebih baru, contoh berikut menggunakan sumber data eksternal yang menunjuk ke kontainer di akun penyimpanan Azure dan kredensial cakupan 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.

G. 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 = '<password>';
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;

H. Menggunakan identitas terkelola untuk sumber eksternal

Berlaku untuk: Azure SQL Managed Instance dan Azure SQL Database

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 (
    LOCATION = 'abs://****************.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;

I. Menggunakan OPENROWSET untuk mengakses beberapa file Parquet 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 Parquet 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;

J. Menggunakan OPENROWSET untuk mengakses beberapa tabel 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;

K. Menggunakan OPENROWSET untuk mengkueri himpunan data publik-anonim

Contoh berikut menggunakan kumpulan data terbuka catatan perjalanan taksi kuning NYC yang tersedia untuk umum.

Buat sumber data terlebih dahulu:

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

Kueri semua file dengan ekstensi dalam folder yang cocok dengan .parquet pola nama:

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Contoh lainnya

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

• Impor dan Ekspor Data Massal (SQL Server)
• Contoh impor dan ekspor massal dokumen XML (SQL Server)
• Pertahankan nilai identitas saat mengimpor data secara massal (SQL Server)
• Menyimpan nilai null atau default selama impor massal (SQL Server)
• Menggunakan file format untuk mengimpor data secara massal (SQL Server)
• Menggunakan format karakter untuk mengimpor atau mengekspor data (SQL Server)
• Gunakan File Format untuk Melewati Kolom Tabel (SQL Server)
• Menggunakan file format untuk melewati bidang data (SQL Server)
• Menggunakan file format untuk memetakan kolom tabel ke bidang file data (SQL Server)
• Mengkueri sumber data menggunakan OPENROWSET di Azure SQL Managed Instances
• Tentukan terminator kolom dan baris (SQL Server)

Konten terkait

• HAPUS (Transact-SQL)
• Klausa FROM ditambah JOIN, APPLY, PIVOT (Transact-SQL)
• SISIPKAN (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)
• OPENQUERY (Transact-SQL)
• PILIH (Transact-SQL)
• UPDATE (Transact-SQL)
• DI MANA (Transact-SQL)