SISIPAN MASSAL (Transact-SQL)

Artikel
03/23/2023

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

Mengimpor file data ke dalam tabel database atau tampilan dalam format yang ditentukan pengguna di SQL Server

Sintaks

BULK INSERT
   { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] DATA_SOURCE = 'data_source_name' ]

   -- text formatting options
   [ [ , ] CODEPAGE = { 'RAW' | 'code_page' | 'ACP' | 'OEM' } ]
   [ [ , ] DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' } ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] FORMAT = 'CSV' ]
   [ [ , ] FIELDQUOTE = 'quote_characters']

   [ [ , ] FIRSTROW = first_row ]
   [ [ , ] LASTROW = last_row ]

   -- input file format options
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] FORMATFILE_DATA_SOURCE = 'data_source_name' ]

   -- error handling options
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ERRORFILE = 'file_name' ]
   [ [ , ] ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name' ]

   -- database options
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] TABLOCK ]

   -- source options
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch 
   [ [ , ] BATCHSIZE = batch_size ]

    )]

Argumen

database_name

Nama database tempat tabel atau tampilan yang ditentukan berada. Jika tidak ditentukan, database_name adalah database saat ini.

schema_name

Menentukan nama tabel atau skema tampilan. schema_name bersifat opsional jika skema default untuk pengguna yang melakukan operasi impor massal adalah skema tabel atau tampilan yang ditentukan. Jika skema tidak ditentukan dan skema default pengguna yang melakukan operasi impor massal berbeda dari tabel atau tampilan yang ditentukan, SQL Server mengembalikan pesan kesalahan, dan operasi impor massal dibatalkan.

table_name

Menentukan nama tabel atau tampilan untuk mengimpor data secara massal. Hanya tampilan di mana semua kolom merujuk ke tabel dasar yang sama yang dapat digunakan. Untuk informasi selengkapnya tentang pembatasan untuk memuat data ke dalam tampilan, lihat INSERT (Transact-SQL).

DARI 'data_file'

Menentukan jalur lengkap file data yang berisi data untuk diimpor ke dalam tabel atau tampilan yang ditentukan. INSERT MASSAL dapat mengimpor data dari disk atau Azure Blob Storage (termasuk jaringan, disket, hard disk, dan sebagainya).

data_file harus menentukan jalur yang valid dari server tempat SQL Server berjalan. Jika data_file adalah file jarak jauh, tentukan nama Universal Naming Convention (UNC). Nama UNC memiliki formulir \\SystemName\ShareName\Path\FileName. Contohnya:

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.dat';

Dimulai dengan SQL Server 2017 (14.x), data_file dapat berada di Azure Blob Storage. Dalam hal ini, Anda perlu menentukan opsi data_source_name . Misalnya, lihat Mengimpor data dari file di Azure Blob Storage.

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

BATCHSIZE = batch_size

Menentukan jumlah baris dalam batch. Setiap batch disalin ke server sebagai satu transaksi. Jika ini gagal, SQL Server menerapkan atau mengembalikan transaksi untuk setiap batch. Secara default, semua data dalam file data yang ditentukan adalah satu batch. Untuk informasi tentang pertimbangan performa, lihat Pertimbangan performa nanti di artikel ini.

CHECK_CONSTRAINTS

Menentukan bahwa semua batasan pada tabel target atau tampilan harus diperiksa selama operasi impor massal. Tanpa opsi CHECK_CONSTRAINTS, batasan CHECK dan FOREIGN KEY apa pun diabaikan, dan setelah operasi, batasan pada tabel ditandai sebagai tidak tepercaya.

Batasan KUNCI UNIK dan PRIMER selalu diberlakukan. Saat mengimpor ke kolom karakter yang ditentukan dengan batasan NOT NULL, INSERT MASSAL menyisipkan string kosong saat tidak ada nilai dalam file teks.

Pada titik tertentu, Anda harus memeriksa batasan pada seluruh tabel. Jika tabel tidak kosong sebelum operasi impor massal, biaya validasi ulang batasan dapat melebihi biaya penerapan batasan CHECK ke data bertahap.

Situasi di mana Anda mungkin ingin batasan dinonaktifkan (perilaku default) adalah jika data input berisi baris yang melanggar batasan. Dengan batasan CHECK dinonaktifkan, Anda dapat mengimpor data lalu menggunakan pernyataan Transact-SQL untuk menghapus data yang tidak valid.

Catatan

Opsi MAXERRORS tidak berlaku untuk pemeriksaan batasan.

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 besar dari 127 atau kurang dari 32. Misalnya, lihat Menentukan halaman kode.

CODEPAGE bukan opsi yang didukung di Linux untuk SQL Server 2017 (14.x). Untuk SQL Server 2019 (15.x), hanya opsi 'RAW' yang diizinkan untuk CODEPAGE.

Anda harus menentukan nama kolater untuk setiap kolom dalam file format.

Nilai CODEPAGE	Deskripsi
ACP	Kolom tipe data karakter, varchar, atau teks dikonversi dari halaman kode ANSI/Microsoft Windows (ISO 1252) ke halaman kode SQL Server.
OEM (default)	Kolom tipe data karakter, varchar, atau teks dikonversi dari halaman kode OEM sistem ke halaman kode SQL Server.
RAW	Tidak ada konversi dari satu halaman kode ke halaman lain yang terjadi. RAW adalah opsi tercepat.
code_page	Nomor halaman kode tertentu, misalnya, 850. Versi sebelum SQL Server 2016 (13.x) tidak mendukung halaman kode 65001 (pengodean UTF-8).

DATAFILETYPE = { 'char' | 'asli' | 'widechar' | 'widenative' }

Menentukan bahwa BULK INSERT melakukan operasi impor menggunakan nilai jenis file data yang ditentukan.

Nilai DATAFILETYPE	Semua data yang diwakili dalam:
karakter (default)	Format karakter. Untuk informasi selengkapnya, lihat Menggunakan Format Karakter untuk Mengimpor atau Mengekspor Data (SQL Server).
asli	Jenis data asli (database). Buat file data asli dengan mengimpor data secara massal dari SQL Server menggunakan utilitas bcp . Nilai asli menawarkan alternatif performa yang lebih tinggi untuk nilai karakter. Format asli disarankan saat Anda mentransfer data secara massal antara beberapa instans SQL Server menggunakan file data yang tidak berisi karakter extended/double-byte character set (DBCS). Untuk informasi selengkapnya, lihat Menggunakan Format Asli untuk Mengimpor atau Mengekspor Data (SQL Server).
widechar	Karakter Unicode. Untuk informasi selengkapnya, lihat Menggunakan Format Karakter Unicode untuk Mengimpor atau Mengekspor Data (SQL Server).
widenative	Jenis data asli (database), kecuali dalam kolom karakter, varchar, dan teks , di mana data disimpan sebagai Unicode. Buat file data yang melebar dengan mengimpor data secara massal dari SQL Server menggunakan utilitas bcp. Nilai widenative menawarkan alternatif performa yang lebih tinggi untuk widechar. Jika file data berisi karakter yang diperluas ANSI, tentukan widenative. Untuk informasi selengkapnya, lihat Menggunakan Format Asli Unicode untuk Mengimpor atau Mengekspor Data (SQL Server).

DATA_SOURCE = 'data_source_name'

Berlaku untuk: SQL Server 2017 (14.x) dan Azure SQL Database.

Menentukan sumber data eksternal bernama yang menunjuk ke lokasi Azure Blob Storage file yang akan diimpor. Sumber data eksternal harus dibuat menggunakan opsi yang TYPE = BLOB_STORAGE ditambahkan di SQL Server 2017 (14.x). Untuk informasi selengkapnya, lihat MEMBUAT SUMBER DATA EKSTERNAL. Misalnya, lihat Mengimpor data dari file di Azure Blob Storage.

ERRORFILE = 'error_file_path'

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 ketika perintah dijalankan. Kesalahan terjadi jika file sudah ada. Selain itu, file kontrol yang memiliki ekstensi .ERROR.txt dibuat, yang mereferensikan setiap baris dalam file kesalahan dan menyediakan diagnostik kesalahan. Segera 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 = 'errorfile_data_source_name'

Berlaku untuk: SQL Server 2017 (14.x).

Menentukan sumber data eksternal bernama yang menunjuk ke lokasi Azure Blob Storage 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). Untuk informasi selengkapnya, lihat MEMBUAT SUMBER DATA EKSTERNAL.

FIRSTROW = first_row

Menentukan jumlah baris pertama yang akan dimuat. Defaultnya adalah baris pertama dalam file data yang ditentukan. FIRSTROW berbasis 1.

Atribut FIRSTROW tidak dimaksudkan untuk melewati header kolom. Melompati header tidak didukung oleh pernyataan INSERT MASSAL. Jika Anda memilih untuk melewati baris, Mesin Database SQL Server hanya melihat terminator bidang, dan tidak memvalidasi data di bidang baris yang dilewati.

FIRE_TRIGGERS

Menentukan bahwa setiap pemicu sisipan yang ditentukan pada tabel tujuan dijalankan selama operasi impor massal. Jika pemicu didefinisikan untuk operasi INSERT pada tabel target, pemicu akan diaktifkan untuk setiap batch yang telah selesai.

Jika FIRE_TRIGGERS tidak ditentukan, tidak ada pemicu sisipan yang dijalankan.

FORMATFILE_DATA_SOURCE = 'data_source_name'

Berlaku untuk: SQL Server 2017 (14.x).

Menentukan sumber data eksternal bernama yang menunjuk ke lokasi Azure Blob Storage dari file format yang akan menentukan skema data yang diimpor. Sumber data eksternal harus dibuat menggunakan opsi yang TYPE = BLOB_STORAGE ditambahkan di SQL Server 2017 (14.x). Untuk informasi selengkapnya, lihat MEMBUAT SUMBER DATA EKSTERNAL.

KEEPIDENTITY

Menentukan bahwa nilai identitas atau nilai dalam file data yang diimpor akan digunakan untuk kolom identitas. Jika KEEPIDENTITY tidak ditentukan, nilai identitas untuk kolom ini diverifikasi tetapi tidak diimpor dan SQL Server secara otomatis menetapkan nilai unik berdasarkan nilai benih dan kenaikan yang ditentukan selama pembuatan tabel. Jika file data tidak berisi nilai untuk kolom identitas dalam tabel atau tampilan, gunakan file format untuk menentukan bahwa kolom identitas dalam tabel atau tampilan akan dilewati saat mengimpor data; SQL Server secara otomatis menetapkan nilai unik untuk kolom tersebut. Untuk informasi selengkapnya, lihat DBCC CHECKIDENT (Transact-SQL).

Untuk informasi selengkapnya, lihat tentang menyimpan nilai identifikasi lihat Menyimpan Nilai Identitas Saat Mengimpor Data Secara Massal (SQL Server).

KEEPNULLS

Menentukan bahwa kolom kosong harus mempertahankan nilai null selama operasi impor massal, alih-alih memiliki nilai default untuk kolom yang disisipkan. Untuk informasi selengkapnya, lihat Menyimpan Null atau Menggunakan Nilai Default Selama Impor Massal (SQL Server).

KILOBYTES_PER_BATCH = kilobytes_per_batch

Menentukan perkiraan jumlah kilobyte (KB) data per batch sebagai kilobytes_per_batch. Secara default, KILOBYTES_PER_BATCH tidak diketahui. Untuk informasi tentang pertimbangan performa, lihat Pertimbangan performa nanti di artikel ini.

LASTROW = last_row

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

MAXERRORS = max_errors

Menentukan jumlah maksimum kesalahan sintaksis yang diizinkan dalam data sebelum operasi impor massal dibatalkan. Setiap baris yang tidak dapat diimpor oleh operasi impor massal diabaikan dan dihitung sebagai satu kesalahan. Jika max_errors tidak ditentukan, defaultnya adalah 10.

Opsi MAX_ERRORS tidak berlaku untuk pemeriksaan batasan atau untuk mengonversi jenis data uang dan bigint .

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

Menentukan bagaimana data dalam file data diurutkan. Performa impor massal ditingkatkan jika data yang diimpor diurutkan sesuai dengan indeks terkluster pada tabel, jika ada. Jika file data diurutkan dalam urutan yang berbeda, itu selain urutan kunci indeks berkluster atau jika tidak ada indeks berkluster pada tabel, klausa ORDER diabaikan. Nama kolom yang disediakan harus berupa nama kolom yang valid dalam tabel tujuan. Secara default, operasi sisipan massal mengasumsikan file data tidak diurutkan. Untuk impor massal yang dioptimalkan, SQL Server juga memvalidasi bahwa data yang diimpor diurutkan.

n adalah tempat penampung yang menunjukkan bahwa beberapa kolom dapat ditentukan.

ROWS_PER_BATCH = rows_per_batch

Menunjukkan perkiraan jumlah baris data dalam file data.

Secara default, semua data dalam file data dikirim ke server sebagai satu transaksi, dan jumlah baris dalam batch tidak diketahui oleh pengoptimal kueri. Jika Anda menentukan ROWS_PER_BATCH (dengan nilai > 0) server menggunakan nilai ini untuk mengoptimalkan operasi impor massal. Nilai yang ditentukan untuk ROWS_PER_BATCH harus kira-kira sama dengan jumlah baris aktual. Untuk informasi tentang pertimbangan performa, lihat Pertimbangan performa nanti di artikel ini.

TABLOCK

Menentukan bahwa kunci tingkat tabel diperoleh selama durasi operasi impor massal. Tabel dapat dimuat secara bersamaan oleh beberapa klien jika tabel tidak memiliki indeks dan TABLOCK ditentukan. Secara default, perilaku penguncian ditentukan oleh kunci tabel opsi tabel pada beban massal. Menahan kunci selama durasi operasi impor massal mengurangi ketidakcocokan kunci pada tabel, dalam beberapa kasus dapat secara signifikan meningkatkan performa. Untuk informasi tentang pertimbangan performa, lihat Pertimbangan performa nanti di artikel ini.

Untuk indeks penyimpan kolom, perilaku penguncian berbeda karena dibagi secara internal menjadi beberapa set baris. Setiap utas memuat data secara eksklusif ke setiap set baris dengan mengambil kunci X pada set baris yang memungkinkan pemuatan data paralel dengan sesi pemuatan data bersamaan. Penggunaan opsi TABLOCK akan menyebabkan utas mengambil kunci X pada tabel (tidak seperti kunci BU untuk set baris tradisional) yang akan mencegah utas bersamaan lainnya memuat data secara bersamaan.

Opsi format file input

FORMAT = 'CSV'

Berlaku untuk: SQL Server 2017 (14.x).

Menentukan file nilai yang dipisahkan koma yang sesuai dengan standar RFC 4180 .

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.csv'
WITH ( FORMAT = 'CSV');

FIELDQUOTE = 'field_quote'

Berlaku untuk: SQL Server 2017 (14.x).

Menentukan karakter yang akan digunakan sebagai karakter kuotasi dalam file CSV. Jika tidak ditentukan, karakter kuotasi (") akan digunakan sebagai karakter kutipan seperti yang didefinisikan dalam standar RFC 4180 .

FORMATFILE = 'format_file_path'

Menentukan jalur lengkap file format. File format menjelaskan file data yang berisi respons tersimpan yang dibuat dengan menggunakan utilitas bcp pada tabel atau tampilan yang sama. File format harus digunakan jika:

File data berisi kolom yang lebih besar atau lebih sedikit daripada tabel atau tampilan.
Kolom berada dalam urutan yang berbeda.
Pemisah kolom bervariasi.
Ada perubahan lain dalam format data. File format biasanya dibuat dengan menggunakan utilitas bcp dan dimodifikasi dengan editor teks sesuai kebutuhan. Untuk informasi selengkapnya, lihat Utilitas bcp dan Membuat file format.

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

FIELDTERMINATOR = 'field_terminator'

Menentukan terminator bidang yang akan digunakan untuk file data karakter dan widechar . Terminator bidang default adalah \t (karakter tab). Untuk informasi selengkapnya, lihat Menentukan Terminator Bidang dan Baris (SQL Server).

ROWTERMINATOR = 'row_terminator'

Menentukan terminator baris yang akan digunakan untuk file data karakter dan widechar . Terminator baris default adalah \r\n (karakter baris baru). Untuk informasi selengkapnya, lihat Menentukan Terminator Bidang dan Baris (SQL Server).

Kompatibilitas

BULK INSERT memberlakukan validasi data yang ketat dan pemeriksaan data yang dibaca dari file yang dapat menyebabkan skrip yang ada gagal saat dijalankan pada data yang tidak valid. Misalnya, BULK INSERT memverifikasi bahwa:

Representasi asli dari jenis data float atau real valid.
Data Unicode memiliki panjang byte genap.

Jenis data

Konversi jenis data string-ke-desimal

Konversi jenis data string-ke-desimal yang digunakan dalam INSERT MASSAL mengikuti aturan yang sama dengan fungsi Transact-SQL CONVERT , yang menolak string yang mewakili nilai numerik yang menggunakan notasi ilmiah. Oleh karena itu, BULK INSERT memperlakukan string seperti nilai yang tidak valid dan melaporkan kesalahan konversi.

Untuk mengatasi perilaku ini, gunakan file format untuk mengimpor data float notasi ilmiah secara massal ke dalam kolom desimal. Dalam file format, jelaskan kolom secara eksplisit sebagai data nyata atau float . Untuk informasi selengkapnya tentang jenis data ini, lihat float dan nyata (Transact-SQL).

File format mewakili data nyata sebagai jenis data SQLFLT4 dan data float sebagai jenis data SQLFLT8 . Untuk informasi tentang file format non-XML, lihat Menentukan Jenis Penyimpanan File dengan Menggunakan bcp (SQL Server).

Contoh mengimpor nilai numerik yang menggunakan notasi ilmiah

Contoh ini menggunakan tabel berikut dalam bulktest database:

CREATE TABLE dbo.t_float(c1 FLOAT, c2 DECIMAL (5,4));

Pengguna ingin mengimpor data secara massal ke t_float dalam tabel. File data, C:\t_float-c.dat, berisi data float notasi ilmiah; misalnya:

8.0000000000000002E-2 8.0000000000000002E-2

Saat menyalin sampel ini, ketahui editor teks dan pengodean yang berbeda yang menyimpan karakter tab (\t) sebagai spasi. Karakter tab diharapkan nanti dalam sampel ini.

Namun, BULK INSERT tidak dapat mengimpor data ini langsung ke , t_floatkarena kolom kedua, c2, menggunakan decimal jenis data. Oleh karena itu, file format diperlukan. File format harus memetakan data float notasi ilmiah ke format desimal kolom c2.

File format berikut menggunakan SQLFLT8 jenis data untuk memetakan bidang data kedua ke kolom kedua:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/> </RECORD> <ROW>
<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLFLT8"/> </ROW> </BCPFORMAT>

Untuk menggunakan file format ini (menggunakan nama C:\t_floatformat-c-xml.xmlfile ) untuk mengimpor data pengujian ke dalam tabel pengujian, terbitkan pernyataan Transact-SQL berikut:

BULK INSERT bulktest.dbo.t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE = 'C:\t_floatformat-c-xml.xml');

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

Jenis data untuk 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 SQLVARCHAR	Data dikirim di halaman kode klien atau di halaman kode yang disiratkan oleh kolase). Efeknya sama dengan menentukan DATAFILETYPE = 'char' tanpa menentukan file format.
SQLNCHAR atau SQLNVARCHAR	Data dikirim sebagai Unicode. Efeknya sama dengan menentukan DATAFILETYPE = 'widechar' tanpa menentukan file format.
SQLBINARY atau SQLVARBIN	Data dikirim tanpa konversi apa pun.

Keterangan

Untuk perbandingan pernyataan INSERT MASSAL, INSERT ... SELECT * FROM OPENROWSET(BULK...) pernyataan, dan perintah bcp , lihat Impor dan Ekspor Data Massal (SQL Server).

Untuk informasi tentang menyiapkan data untuk impor massal, lihat Menyiapkan Data untuk Ekspor atau Impor Massal (SQL Server).

Pernyataan INSERT MASSAL dapat dijalankan dalam transaksi yang ditentukan pengguna untuk mengimpor data ke dalam tabel atau tampilan. Secara opsional, untuk menggunakan beberapa kecocokan untuk mengimpor data secara massal, transaksi dapat menentukan klausa BATCHSIZE dalam pernyataan INSERT MASSAL. Jika transaksi multi-batch digulung balik, setiap batch yang telah dikirim transaksi ke SQL Server digulung balik.

Interoperabilitas

Mengimpor data dari file CSV

Dimulai dengan SQL Server 2017 (14.x), BULK INSERT mendukung format CSV, seperti halnya Azure SQL Database.

Sebelum SQL Server 2017 (14.x), file nilai yang dipisahkan koma (CSV) tidak didukung oleh operasi impor massal SQL Server. Namun, dalam beberapa kasus, file CSV dapat digunakan sebagai file data untuk impor data massal ke SQL Server. Untuk informasi tentang persyaratan untuk mengimpor data dari file data CSV, lihat Menyiapkan Data untuk Ekspor atau Impor Massal (SQL Server).

Perilaku log

Untuk informasi tentang kapan operasi penyisipan baris yang dilakukan oleh impor massal ke SQL Server dicatat dalam log transaksi, lihat Prasyarat untuk Pengelogan Minimal dalam Impor Massal. Pengelogan minimal tidak didukung di Azure SQL Database.

Batasan

Saat menggunakan file format dengan INSERT MASSAL, Anda dapat menentukan hingga 1024 bidang saja. Ini sama dengan jumlah maksimum kolom yang diizinkan dalam tabel. Jika Anda menggunakan file format dengan BULK INSERT dengan file data yang berisi lebih dari 1024 bidang, BULK INSERT menghasilkan kesalahan 4822. Utilitas bcp tidak memiliki batasan ini, jadi untuk file data yang berisi lebih dari 1024 bidang, gunakan INSERT MASSAL tanpa file format atau gunakan perintah bcp.

Pertimbangan performa

Jika jumlah halaman yang akan disiram dalam satu batch melebihi ambang batas internal, pemindaian penuh kumpulan buffer mungkin terjadi untuk mengidentifikasi halaman mana yang akan disiram ketika batch berkomitmen. Pemindaian penuh ini dapat melukai performa impor massal. Kemungkinan kasus melebihi ambang batas internal terjadi ketika kumpulan buffer besar dikombinasikan dengan subsistem I/O yang lambat. Untuk menghindari luapan buffer pada komputer besar, jangan gunakan petunjuk TABLOCK (yang akan menghapus pengoptimalan massal) atau menggunakan ukuran batch yang lebih kecil (yang mempertahankan pengoptimalan massal).

Anda harus menguji berbagai ukuran batch dengan beban data Anda untuk mengetahui apa yang paling sesuai untuk Anda. Perlu diingat bahwa ukuran batch memiliki implikasi putar kembali parsial. Jika proses Anda gagal dan sebelum Anda menggunakan BULK INSERT lagi, Anda mungkin harus melakukan pekerjaan manual tambahan untuk menghapus bagian dari baris yang berhasil disisipkan, sebelum kegagalan terjadi.

Dengan Azure SQL Database, pertimbangkan untuk sementara meningkatkan tingkat performa database atau instans sebelum mengimpor jika Anda mengimpor data dalam volume besar.

Keamanan

Delegasi akun keamanan (peniruan identitas)

Jika pengguna menggunakan login SQL Server, profil keamanan akun proses SQL Server digunakan. Login menggunakan autentikasi SQL Server tidak dapat diautentikasi di luar Mesin Database. Oleh karena itu, ketika perintah BULK INSERT dimulai oleh login menggunakan autentikasi SQL Server, koneksi ke data dibuat menggunakan konteks keamanan akun proses SQL Server (akun yang digunakan oleh layanan Mesin Database SQL Server).

Agar berhasil membaca data sumber, Anda harus memberikan akun yang digunakan oleh Mesin Database SQL Server, akses ke data sumber. Sebaliknya, jika pengguna SQL Server masuk dengan menggunakan Autentikasi Windows, pengguna hanya dapat membaca file-file yang dapat diakses oleh akun pengguna, terlepas dari profil keamanan proses SQL Server.

Saat menjalankan pernyataan INSERT MASSAL dengan menggunakan sqlcmd atau osql, dari satu komputer, menyisipkan data ke SQL Server di komputer kedua, dan menentukan data_file di komputer ketiga dengan menggunakan jalur UNC, Anda mungkin menerima kesalahan 4861.

Untuk mengatasi kesalahan ini, gunakan Autentikasi SQL Server dan tentukan login SQL Server yang menggunakan profil keamanan akun proses SQL Server, atau konfigurasikan Windows untuk mengaktifkan delegasi akun keamanan. Untuk informasi tentang cara mengaktifkan akun pengguna agar tepercaya untuk delegasi, lihat Bantuan Windows.

Untuk informasi selengkapnya tentang ini dan pertimbangan keamanan lainnya untuk menggunakan SISIPAN MASSAL, lihat Mengimpor Data Massal dengan Menggunakan SISIPAN MASSAL atau OPENROWSET(MASSAL...) (SQL Server).

Saat mengimpor dari Azure Blob Storage dan data tidak publik (akses anonim), buat KREDENSIAL CAKUPAN DATABASE berdasarkan kunci SAS yang dienkripsi dengan KUNCI MASTER, lalu buat sumber database eksternal untuk digunakan dalam perintah INSERT MASSAL Anda.

Atau, buat KREDENSIAL CAKUPAN DATABASE berdasarkan MANAGED IDENTITY untuk mengotorisasi permintaan akses data di akun penyimpanan non-publik. Saat menggunakan MANAGED IDENTITY, penyimpanan Azure harus memberikan izin ke identitas terkelola instans dengan menambahkan peran Kontrol akses berbasis peran Azure (RBAC) Kontributor Data Blob Penyimpanan bawaan yang menyediakan akses baca/tulis ke identitas terkelola untuk kontainer Azure Blob Storage yang diperlukan. Azure SQL Managed Instance memiliki identitas terkelola yang ditetapkan sistem, dan juga dapat memiliki satu atau beberapa identitas terkelola yang ditetapkan pengguna. Anda dapat menggunakan identitas terkelola yang ditetapkan sistem atau identitas terkelola yang ditetapkan pengguna untuk mengotorisasi permintaan. Untuk otorisasi, default identitas instans terkelola akan digunakan (yaitu identitas terkelola utama yang ditetapkan pengguna, atau identitas terkelola yang ditetapkan sistem jika identitas terkelola yang ditetapkan pengguna tidak ditentukan). Misalnya, lihat Mengimpor data dari file di Azure Blob Storage.

Penting

Identitas Terkelola hanya berlaku untuk Azure SQL. SQL Server tidak mendukung Identitas Terkelola.

Izin

Memerlukan izin INSERT dan ADMINISTER BULK OPERATIONS. Di Azure SQL Database, izin INSERT dan ADMINISTER DATABASE BULK OPERATIONS diperlukan. Mengelola izin OPERASI MASSAL atau peran bulkadmin tidak didukung untuk SQL Server di Linux. Hanya sysadmin yang dapat melakukan sisipan massal untuk SQL Server di Linux.

Selain itu, izin ALTER TABLE diperlukan jika satu atau beberapa kondisi berikut ini benar:

Batasan ada dan opsi CHECK_CONSTRAINTS tidak ditentukan.

Menonaktifkan batasan adalah perilaku default. Untuk memeriksa batasan secara eksplisit, gunakan opsi CHECK_CONSTRAINTS.
Pemicu ada dan opsi FIRE_TRIGGER tidak ditentukan.

Secara default, pemicu tidak diaktifkan. Untuk mengaktifkan pemicu secara eksplisit, gunakan opsi FIRE_TRIGGER.
Anda menggunakan opsi KEEPIDENTITY untuk mengimpor nilai identitas dari file data.

Contoh

J. Menggunakan pipa untuk mengimpor data dari file

Contoh berikut mengimpor informasi detail pesanan ke dalam AdventureWorks2022.Sales.SalesOrderDetail tabel dari file data yang ditentukan dengan menggunakan pipa (|) sebagai terminator bidang dan |\n sebagai terminator baris.

BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
      (
         FIELDTERMINATOR = ' |'
         , ROWTERMINATOR = ' |\n'
      );

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

B. Menggunakan argumen FIRE_TRIGGERS

Contoh berikut menentukan FIRE_TRIGGERS argumen .

BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
         FIELDTERMINATOR = ' |'
         , ROWTERMINATOR = ':\n'
         , FIRE_TRIGGERS
      );

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

C. Menggunakan umpan baris sebagai terminator baris

Contoh berikut mengimpor file yang menggunakan umpan baris sebagai terminator baris seperti output UNIX:

DECLARE @bulk_cmd VARCHAR(1000);
SET @bulk_cmd = 'BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>''
WITH (ROWTERMINATOR = '''+CHAR(10)+''')';
EXEC(@bulk_cmd);

Catatan

Karena cara Microsoft Windows memperlakukan file teks, \n secara otomatis diganti dengan \r\n.

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

D. Tentukan halaman kode

Contoh berikut menunjukkan cara menentukan halaman kode.

BULK INSERT MyTable
FROM 'D:\data.csv'
WITH
( CODEPAGE = '65001'
   , DATAFILETYPE = 'char'
   , FIELDTERMINATOR = ','
);

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

E. Mengimpor data dari file CSV

Contoh berikut menunjukkan cara menentukan file CSV, melewati header (baris pertama), menggunakan ; sebagai terminator bidang dan 0x0a sebagai terminator baris:

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH (FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

Contoh berikut menunjukkan cara menentukan file CSV dalam format UTF-8 (menggunakan CODEPAGE ), 65001melewati header (baris pertama), menggunakan ; sebagai terminator bidang dan 0x0a sebagai terminator baris:

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH ( CODEPAGE = '65001'
      , FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

Penting

Azure SQL Database hanya mendukung pembacaan dari Azure Blob Storage.

F. Mengimpor data dari file di Azure Blob Storage

Contoh berikut menunjukkan cara memuat data dari file CSV di lokasi Azure Blob Storage tempat Anda membuat Tanda Tangan Akses Bersama (SAS). Lokasi Azure Blob Storage dikonfigurasi sebagai sumber data eksternal, yang memerlukan kredensial cakupan database menggunakan kunci SAS 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/invoices'
          , CREDENTIAL = MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

Contoh berikut menunjukkan cara menggunakan perintah INSERT MASSAL untuk memuat data dari file csv di lokasi penyimpanan Azure Blob menggunakan Identitas Terkelola. Lokasi penyimpanan Azure Blob dikonfigurasi sebagai sumber data eksternal.

--> 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 = 'Managed Identity';
-- NOTE: Make sure you have granted Storage Bob Data Contributor RBAC on storage to provides read/write access to the managed identity for the necessary Azure Blob Storage containers.
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);
BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

Penting

Identitas Terkelola hanya berlaku untuk Azure SQL. SQL Server tidak mendukung Identitas Terkelola.

Penting

Azure SQL hanya mendukung pembacaan dari Azure Blob Storage.

G. Mengimpor data dari file di Azure Blob Storage dan menentukan file kesalahan

Contoh berikut menunjukkan cara memuat data dari file CSV di lokasi Azure Blob Storage, yang telah dikonfigurasi sebagai sumber data eksternal, dan juga menentukan file kesalahan. Anda akan memerlukan kredensial lingkup database menggunakan tanda tangan akses bersama. Jika berjalan di Azure SQL Database, opsi ERRORFILE harus disertai dengan ERRORFILE_DATA_SOURCE jika tidak, impor mungkin gagal dengan kesalahan izin. File yang ditentukan dalam ERRORFILE seharusnya tidak ada dalam kontainer.

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (
         DATA_SOURCE = 'MyAzureInvoices'
         , FORMAT = 'CSV'
         , ERRORFILE = 'MyErrorFile'
         , ERRORFILE_DATA_SOURCE = 'MyAzureInvoices');

Untuk contoh lengkap BULK INSERT termasuk mengonfigurasi kredensial dan sumber data eksternal, lihat Contoh Akses Massal ke Data di Azure Blob Storage.

Contoh lainnya

Contoh lain BULK INSERT disediakan dalam artikel berikut:

Bagikan melalui

SISIPAN MASSAL (Transact-SQL)

Sintaks

Argumen

database_name

schema_name

table_name

DARI 'data_file'

BATCHSIZE = batch_size

CHECK_CONSTRAINTS

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

DATAFILETYPE = { 'char' | 'asli' | 'widechar' | 'widenative' }

DATA_SOURCE = 'data_source_name'

ERRORFILE = 'error_file_path'

ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name'

FIRSTROW = first_row

FIRE_TRIGGERS

FORMATFILE_DATA_SOURCE = 'data_source_name'

KEEPIDENTITY

KEEPNULLS

KILOBYTES_PER_BATCH = kilobytes_per_batch

LASTROW = last_row

MAXERRORS = max_errors

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

ROWS_PER_BATCH = rows_per_batch

TABLOCK

Opsi format file input

FORMAT = 'CSV'

FIELDQUOTE = 'field_quote'

FORMATFILE = 'format_file_path'

FIELDTERMINATOR = 'field_terminator'

ROWTERMINATOR = 'row_terminator'

Kompatibilitas

Jenis data

Konversi jenis data string-ke-desimal

Contoh mengimpor nilai numerik yang menggunakan notasi ilmiah

Jenis data untuk mengekspor atau mengimpor dokumen SQLXML secara massal

Keterangan

Interoperabilitas

Mengimpor data dari file CSV

Perilaku log

Batasan

Pertimbangan performa

Keamanan

Delegasi akun keamanan (peniruan identitas)

Izin

Contoh

J. Menggunakan pipa untuk mengimpor data dari file

B. Menggunakan argumen FIRE_TRIGGERS

C. Menggunakan umpan baris sebagai terminator baris

D. Tentukan halaman kode

E. Mengimpor data dari file CSV

F. Mengimpor data dari file di Azure Blob Storage

G. Mengimpor data dari file di Azure Blob Storage dan menentukan file kesalahan

Contoh lainnya

Lihat juga

Saran dan Komentar

Sumber Daya Tambahan: