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. Tergantung pada layanannya, file dapat disimpan di Azure Blob Storage, Azure Data Lake, disk lokal, share jaringan, dan lainnya. Anda dapat membaca berbagai format file seperti text/CSV, Parquet, atau JSON-lines.

Anda dapat merujuk OPENROWSET fungsi dalam FROM klausa kueri seolah-olah itu adalah nama tabel. Gunakan untuk membaca data dalam pernyataanSELECT, atau memperbarui data target dalam UPDATEpernyataan , INSERT, DELETE, , CTASMERGE, atau CETAS pernyataan.

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

Tip

Artikel ini dan sintaksnya OPENROWSET(BULK) berbeda pada platform yang berbeda dari SQL Database Engine.

Untuk sintaks Microsoft Fabric Data Warehouse, pilih Fabric Data Warehouse 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

Beberapa OPENROWSET opsi spesifik format, sementara yang lain bersifat universal. Misalnya, pemisah baris dan bidang hanya bermakna untuk teks yang dibatasi (CSV/TSV), sedangkan opsi seperti DATA_SOURCE dan MAXERRORS berlaku untuk semua format. Tabel berikut merangkum opsi yang didukung untuk format yang paling umum.

Opsi	CSV(1.0)	CSV(2.0)	PARKET	JSONL
DATA_SOURCE, ROWS_PER_BATCH, MAXERRORS	Supported	Supported	Supported	Supported
ERRORFILE, ERRORFILE_DATA_SOURCE, FORMATFILE, FORMATFILE_DATA_SOURCE	Supported	Supported	Tidak didukung	Supported
CODEPAGE, TIPE DATAFILE	Supported	Supported	Tidak didukung	Supported
BARIS PERTAMA	Supported	Supported	Tidak didukung	Supported
ROWTERMINATOR, FIELDTERMINATOR, FIELDQUOTE, ESCAPECHAR	Supported	Supported	Tidak didukung	Tidak didukung
Versi Pengurai	Supported	Supported	Tidak didukung	Tidak didukung
BARIS TERAKHIR	Supported	Tidak didukung	Tidak didukung	Tidak didukung
HEADER_ROW	Tidak didukung	Supported	Tidak didukung	Tidak didukung
SINGLE_BLOB, SINGLE_CLOB, SINGLE_NCLOB	Tidak didukung	Tidak didukung	Tidak didukung	Tidak didukung

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 diperlukan opsi ini BULK , lihat bagian Izin di bagian selanjutnya dalam artikel ini.

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

BULK

Jalur atau URI dari file data yang OPENROWSET dibaca dan dikembalikan sebagai set 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 Data Warehouse 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, sehingga OPENROWSET pola dapat dicocokkan dengan URI. Selain itu, URI dapat diakhiri dengan /** untuk mengaktifkan perjalanan 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'
);

Tabel berikut menunjukkan tipe penyimpanan yang dapat direferensikan oleh URI:

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.
Microsoft Fabric Warehouse di titik akhir analitik Microsoft Fabric dan SQL di Microsoft Fabric	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 dalam BULK jalur. Buat sumber data dengan CREATE EXTERNAL DATA SOURCE.

Selain lokasi root, perangkat lunak ini dapat mendefinisikan kredensial khusus 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 halaman kode tertentu:

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.
Integer	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 single-byte (ASCII, UTF8) atau multibyte (UTF16). Nilai yang valid adalah karakter dan widechar:

DATAFILETYPE Nilai	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.
Microsoft Fabric Warehouse di titik akhir analitik Microsoft Fabric dan SQL di Microsoft Fabric	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'
)

Anda membutuhkan file format untuk mendefinisikan tipe kolom dalam set hasil. Satu-satunya pengecualian adalah ketika Anda menentukan SINGLE_CLOB, SINGLE_BLOB, atau SINGLE_NCLOB; dalam kasus ini, Anda tidak memerlukan format file.

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

Mulai dari SQL Server 2017 (14.x), dapat format_file_path 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. Dengan menggunakan sumber data ini, Anda dapat menggunakan jalur relatif dalam FORMATFILE opsi.

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

Buat sumber data file format dengan CREATE EXTERNAL DATA SOURCE. Selain lokasi root, perangkat lunak ini dapat mendefinisikan kredensial khusus 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:

KUTIPAN LAPANGAN

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

FILE KESALAHAN

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

MAXERRORS

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

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

BARIS TERAKHIR

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

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 sebenarnya dalam file data tidak diurutkan sesuai urutan yang Anda tentukan, atau jika Anda menentukan UNIQUE petunjuk dan kunci duplikat ada, kesalahan akan dikembalikan.

Alias kolom diperlukan saat Anda menggunakan ORDER. Daftar alias kolom harus merujuk pada tabel turunan yang diakses oleh klausa tersebut BULK . Nama kolom yang Anda tentukan dalam ORDER klausa merujuk pada daftar alias kolom ini. Anda tidak dapat menentukan tipe nilai besar (varchar(max), nvarchar(max), varbinary(max), dan xml) serta jenis objek besar (LOB) (teks, nteks, dan gambar).

Opsi konten

SINGLE_BLOB

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

Important

Impor data XML hanya dengan menggunakan SINGLE_BLOB opsi, bukan SINGLE_CLOB dan SINGLE_NCLOB, karena hanya SINGLE_BLOB mendukung semua konversi pengkodean Windows.

SINGLE_CLOB

Membaca data_file sebagai ASCII dan mengembalikan isi sebagai satu baris dan satu kolom dengan tipe varchar(max), menggunakan kolasi database saat ini.

SINGLE_NCLOB

Membaca data_file sebagai Unicode dan mengembalikan isi sebagai satu baris dan satu kolom dengan tipe nvarchar(max), menggunakan kolasi dari database saat ini.

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

Permissions

Untuk menggunakan OPENROWSET dengan sumber data eksternal, Anda 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, Anda juga harus menetapkan keanggotaan dalam peran Storage Blob Data Reader (atau lebih tinggi) kepada principal di tingkat kontainer atau akun penyimpanan.

Remarks

• Klausa FROM yang Anda gunakan dapat SELECT memanggil OPENROWSET(BULK...) sebagai pengganti nama tabel, dengan fungsi penuh SELECT .

• OPENROWSET BULK dengan opsi memerlukan nama korelasi, juga dikenal sebagai variabel rentang atau alias, dalam FROM klausa. Jika Anda tidak menambahkan AS <table_alias>, Anda akan mendapatkan pesan kesalahan 491: "Nama korelasi harus ditentukan untuk baris bulk dalam klausa from."

• Anda dapat menentukan alias kolom. Jika Anda tidak menentukan daftar alias kolom, file format harus memiliki nama kolom. Menentukan alias kolom akan menggantikan nama kolom dalam file format. Contohnya:

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

• Sebuah SELECT...FROM OPENROWSET(BULK...) pernyataan dapat mencantumkan alias kolom massal dengan menggunakan file format untuk menentukan nama kolom dan tipe data.

• Dengan menggunakan OPENROWSET(BULK...) sebagai tabel sumber dalam INSERT pernyataan or MERGE , Anda 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.
• Saat Anda menggunakan opsi dengan OPENROWSET BULKINSERT pernyataan, klausa mendukung BULK 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 sisipan baris yang dilakukan impor massal dicatat dalam log transaksi, lihat Prasyarat untuk logging minimal dalam impor massal.
• Saat Anda mengimpor OPENROWSET (BULK ...) data dengan model pemulihan penuh, itu tidak mengoptimalkan pencatatan.

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...) diasumsikan bahwa, jika Anda tidak menentukan lain, panjang SQLCHARmaksimum , SQLNCHAR, atau SQLBINARY data tidak melebihi 8.000 byte. Jika Anda mengimpor data dalam 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 mendefinisikan panjang maksimum untuk bidang data tersebut. 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 mengembalikan nama file serta path dalam set hasil. Atau Anda dapat menggunakannya untuk memfilter data berdasarkan nama file dan jalur folder. Di bagian berikut, Anda akan menemukan deskripsi singkat beserta sampel-contoh.

Fungsi nama file

Fungsi ini mengembalikan nama file untuk baris tersebut.

Tipe data pengembalian adalah nvarchar(1024). Untuk kinerja optimal, selalu cast hasil fungsi filename ke tipe data yang sesuai. Jika Anda menggunakan tipe data karakter, pastikan Anda menggunakan panjang yang sesuai.

Contoh berikut membaca file data NYC Yellow Taxi selama tiga bulan terakhir tahun 2017 dan mengembalikan jumlah perjalanan per file. OPENROWSET Bagian dari 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 cara menggunakan filename() 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 sama seperti 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 sebagian jalur:

• Ketika Anda memanggil filepath fungsi tanpa parameter, fungsi tersebut mengembalikan jalur file lengkap yang menjadi sumber baris.
• Ketika Anda memanggil filepath fungsi dengan parameter, fungsi tersebut mengembalikan bagian jalur yang sesuai dengan wildcard pada posisi yang ditentukan dalam parameter tersebut. Sebagai contoh, nilai parameter 1 mengembalikan bagian jalur yang cocok dengan wildcard pertama.

Tipe data pengembalian fungsi ini filepath adalah nvarchar(1024). Untuk kinerja optimal, selalu cast hasil filepath fungsi ke tipe data yang sesuai. Jika Anda menggunakan tipe data karakter, pastikan Anda menggunakan panjang yang sesuai.

Contoh berikut membaca file data NYC Yellow Taxi selama tiga bulan terakhir tahun 2017. Ini mengembalikan jumlah perjalanan per jalur file. OPENROWSET Bagian dari 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 cara menggunakan filepath() dalam WHERE klausa untuk memfilter file yang akan dibaca.

Anda dapat menggunakan wildcard di OPENROWSET bagian kueri dan memfilter file dalam klausa tersebut WHERE . 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. Gunakan OPENROWSET untuk BULK INSERT mengajukan data ke dalam kolom varbinary(max)

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 bisa digunakan dengan versi 64-bit dari 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 perintah untuk OPENROWSET memuat data dari file CSV di lokasi penyimpanan Azure Blob tempat Anda membuat kunci SAS. Anda mengonfigurasi lokasi penyimpanan Azure Blob sebagai sumber data eksternal. Proses ini memerlukan kredensial berlingkup database yang menggunakan shared access signature (SAS) yang dienkripsi dengan menggunakan kunci master di 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 mengakses beberapa file Parquet dari lokasi 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

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