Bagikan melalui

Gunakan BULK INSERT atau OPENROWSET(BULK...) untuk mengimpor data ke SQL Server

  • Artikel

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

Artikel ini memberikan gambaran umum tentang cara menggunakan pernyataan TRANSACT-SQL BULK INSERT dan INSERT... SELECT * FROM OPENROWSET(BULK...) pernyataan untuk mengimpor data secara massal dari file data ke dalam tabel SQL Server atau Azure SQL Database. Artikel ini juga menjelaskan pertimbangan keamanan untuk menggunakan BULK INSERT dan OPENROWSET(BULK...), dan menggunakan metode ini untuk mengimpor secara massal dari sumber data jarak jauh.

Catatan

Ketika Anda menggunakan BULK INSERT atau OPENROWSET(BULK...), penting untuk memahami bagaimana versi SQL Server menangani peniruan. Untuk informasi selengkapnya, lihat "Pertimbangan Keamanan," nanti dalam topik ini.

Pernyataan BULK INSERT

INSERT MASSAL memuat data dari file data ke dalam tabel. Fungsionalitas ini mirip dengan yang disediakan oleh opsi dalam perintah bcp; namun, file data dibaca oleh proses SQL Server. Untuk deskripsi sintaks INSERT MASSAL, lihat INSERT MASSAL (Transact-SQL).

Contoh INSERT MASSAL

OPENROWSET(MASSAL...) Fungsi

Penyedia set baris massal OPENROWSET diakses dengan memanggil fungsi OPENROWSET dan menentukan opsi MASSAL. Fungsi OPENROWSET(BULK...) memungkinkan Anda mengakses data jarak jauh dengan menyambungkan ke sumber data jarak jauh, seperti file data, melalui penyedia OLE DB.

Untuk mengimpor data secara massal, panggil OPENROWSET(BULK...) dari SELECT... Klausa FROM dalam pernyataan INSERT. Sintaks dasar untuk mengimpor data secara massal adalah:

MEMASUKKAN... PILIH * DARI OPENROWSET(MASSAL...)

Saat digunakan dalam pernyataan INSERT, OPENROWSET(BULK...) mendukung petunjuk tabel. Selain petunjuk tabel reguler, seperti TABLOCK, klausa BULK dapat menerima petunjuk tabel khusus berikut: IGNORE_CONSTRAINTS (hanya mengabaikan batasan CHECK), IGNORE_TRIGGERS, KEEPDEFAULTS, dan KEEPIDENTITY. Untuk informasi selengkapnya, lihat Petunjuk Tabel (Transact-SQL).

Untuk informasi tentang penggunaan tambahan opsi MASSAL, lihat OPENROWSET (Transact-SQL).

MEMASUKKAN... SELECT * FROM OPENROWSET(BULK...) pernyataan - contoh

Pertimbangan keamanan

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.

Misalnya, pertimbangkan pengguna yang masuk ke instans SQL Server dengan menggunakan Autentikasi Windows. Agar pengguna dapat menggunakan BULK INSERT atau OPENROWSET untuk mengimpor data dari file data ke dalam tabel SQL Server, akun pengguna memerlukan akses baca ke file data. Dengan akses ke file data, pengguna dapat mengimpor data dari file ke dalam tabel meskipun proses SQL Server tidak memiliki izin untuk mengakses file. Pengguna tidak perlu memberikan izin akses file ke proses SQL Server.

SQL Server dan Microsoft Windows dapat dikonfigurasi untuk mengaktifkan instans SQL Server untuk terhubung ke instans SQL Server lain dengan meneruskan kredensial pengguna Windows yang diautentikasi. Pengaturan ini dikenal sebagai peniruan identitas atau delegasi. Memahami bagaimana versi SQL Server menangani keamanan untuk peniruan pengguna penting saat Anda menggunakan BULK INSERT atau OPENROWSET. Peniruan pengguna memungkinkan file data berada di komputer yang berbeda dari proses SQL Server atau pengguna. Misalnya, jika pengguna di Computer_A memiliki akses ke file data di Computer_B, dan delegasi kredensial telah diatur dengan tepat, pengguna dapat terhubung ke instans SQL Server yang berjalan di Computer_C, mengakses file data di Computer_B, dan mengimpor data secara massal dari file tersebut ke dalam tabel di Computer_C.

Mengimpor secara massal ke SQL Server dari file data jarak jauh

Untuk menggunakan SISIPAN MASSAL atau SISIPKAN... SELECT * FROM OPENROWSET(BULK...) untuk mengimpor data secara massal dari komputer lain, file data harus dibagikan di antara kedua komputer. Untuk menentukan file data bersama, gunakan nama konvensi penamaan universal (UNC), yang mengambil formulir umum, \\Nama Server nama\Sharename\Path\Filename. Selain itu, akun yang digunakan untuk mengakses file data harus memiliki izin yang diperlukan untuk membaca file pada disk jarak jauh.

Misalnya, pernyataan berikut mengimpor BULK INSERT data secara massal ke SalesOrderDetail dalam tabel AdventureWorks database dari file data yang diberi nama newdata.txt. File data ini berada di folder bersama bernama \dailyorders pada direktori berbagi jaringan bernama salesforce pada sistem bernama computer2.

BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';

Catatan

Pembatasan ini tidak berlaku untuk utilitas bcp karena klien membaca file secara independen dari SQL Server.

Mengimpor secara massal dari penyimpanan Azure Blob

Saat mengimpor dari penyimpanan Azure Blob 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 nonpublik. 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).

Penting

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

Catatan

Jangan gunakan transaksi eksplisit, atau Anda menerima kesalahan 4861.

Menggunakan SISIPAN MASSAL

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

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

 -- NOTE: Make sure that you don't have a leading ? in SAS token, and
 -- that you have at least read permission on the object that should be loaded srt=o&sp=r, and
 -- that expiration period is valid (all dates are in UTC time)

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/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.

Azure SQL Database tidak mendukung pembacaan dari file Windows.

Menggunakan OPENROWSET

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

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

 -- NOTE: Make sure that you don't have a leading ? in SAS token, and
 -- that you have at least read permission on the object that should be loaded srt=o&sp=r, and
 -- that expiration period is valid (all dates are in UTC time)

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

INSERT INTO achievements with (TABLOCK) (id, description)
SELECT * FROM OPENROWSET(
   BULK  'csv/achievements.csv',
   DATA_SOURCE = 'MyAzureBlobStorage',
   FORMAT ='CSV',
   FORMATFILE='csv/achievements-c.xml',
   FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
    ) AS DataFile;

Penting

Azure SQL Database tidak mendukung pembacaan dari file Windows.

Konten terkait