Bagikan melalui

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

Berlaku untuk: SQL ServerAzure SQL DatabaseAzure 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.

Pernyataan BULK INSERT

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

Contoh Pemasukan Massal

Fungsi OPENROWSET(BULK...)

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

Untuk mengimpor data secara massal, panggil OPENROWSET(BULK...) dari SELECT...FROM klausul dalam INSERT pernyataan.

Sintaks dasar untuk mengimpor data secara massal adalah:

INSERT ... SELECT * FROM OPENROWSET(BULK...)

Saat digunakan dalam pernyataan INSERT , OPENROWSET(BULK...) mendukung petunjuk tabel. Selain petunjuk tabel reguler, seperti TABLOCK, BULK klausa dapat menerima petunjuk tabel khusus berikut:

  • IGNORE_CONSTRAINTS (hanya mengabaikan batasan CHECK)
  • IGNORE_TRIGGERS
  • KEEPDEFAULTS
  • KEEPIDENTITY

Untuk informasi selengkapnya, lihat Petunjuk tabel (Transact-SQL).

Untuk informasi tentang penggunaan tambahan opsi BULK, lihat OPENROWSET BULK (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 BULK INSERT perintah 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 BULK INSERT atau INSERT...SELECT * FROM OPENROWSET(BULK...) mengimpor data secara massal dari komputer lain, file data harus dibagi antara kedua komputer. Untuk menentukan file data bersama, gunakan nama konvensi penamaan universal (UNC), yang berbentuk umum, \\Servername\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 bcp utilitas 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 CREATE DATABASE SCOPED CREDENTIAL berdasarkan kunci SAS yang dienkripsi dengan CREATE MASTER KEY, lalu buat sumber database eksternal untuk digunakan dalam perintah Anda BULK INSERT .

Atau, buat CREATE DATABASE SCOPED CREDENTIAL 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 bawaan Azure RBAC Kontributor Data Blob Penyimpanan 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 tidak didukung pada versi SQL Server sebelum SQL Server 2025.

Catatan

Jangan gunakan transaksi eksplisit, atau Anda menerima kesalahan 4861.

Gunakan PENYISIPAN MASSAL

Contoh berikut menunjukkan cara menggunakan BULK INSERT 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 = '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 BULK INSERT perintah 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

Azure SQL Database tidak mendukung pembacaan dari file Windows.

Menggunakan OPENROWSET

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

  • Last updated on