Udostępnij za pośrednictwem

Importowanie danych do programu SQL Server za pomocą funkcji BULK INSERT lub OPENROWSET(BULK...)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Ten artykuł zawiera omówienie sposobu użycia instrukcji Transact-SQL BULK INSERT i instrukcji INSERT...SELECT * FROM OPENROWSET(BULK...) w celu masowego importu danych z pliku danych do tabeli w serwerze SQL Server lub Azure SQL Database.

W tym artykule opisano również zagadnienia dotyczące zabezpieczeń dotyczące używania elementów BULK INSERT i OPENROWSET(BULK...)oraz używanie tych metod do zbiorczego importowania ze zdalnego źródła danych.

INSTRUKCJA BULK INSERT

BULK INSERT ładuje dane z pliku danych do tabeli. Ta funkcja jest podobna do tej udostępnionej przez opcję in polecenia bcp. Jednak plik danych jest odczytywany przez proces programu SQL Server. Aby uzyskać opis BULK INSERT składni, zobacz BULK INSERT (Transact-SQL).

Przykłady operacji BULK INSERT

Funkcja OPENROWSET(BULK...)

Dostęp do dostawcy zbiorczego zestawu wierszy jest uzyskiwany przez wywołanie funkcji OPENROWSET i określenie opcji OPENROWSET. Funkcja OPENROWSET(BULK...) umożliwia dostęp do danych zdalnych, łącząc się ze zdalnym źródłem danych, takim jak plik danych, za pośrednictwem dostawcy danych.

Aby zbiorczo importować dane, wywołaj OPENROWSET(BULK...) z klauzuli SELECT...FROM w instrukcji INSERT.

Podstawowa składnia zbiorczego importowania danych to:

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

W przypadku użycia w instrukcji INSERTOPENROWSET(BULK...) obsługuje wskazówki dotyczące tabeli. Oprócz zwykłych wskazówek tabeli, takich jak TABLOCK, klauzula BULK może akceptować następujące wyspecjalizowane wskazówki tabeli:

  • IGNORE_CONSTRAINTS (ignoruje tylko ograniczenia CHECK)
  • IGNORE_TRIGGERS
  • KEEPDEFAULTS
  • KEEPIDENTITY

Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące tabel (Transact-SQL).

Aby uzyskać informacje na temat dodatkowych zastosowań opcji BULK, zobacz OPENROWSET BULK (Transact-SQL).

INSERT... SELECT * FROM OPENROWSET(BULK...) — przykłady

Zagadnienia dotyczące zabezpieczeń

Jeśli użytkownik loguje się przy pomocy SQL Server, używany jest profil zabezpieczeń konta procesu SQL Server. Nie można uwierzytelnić logowania przy użyciu uwierzytelniania SQL Server poza silnikiem bazy danych. W związku z tym, gdy BULK INSERT polecenie jest inicjowane przez logowanie przy użyciu uwierzytelniania programu SQL Server, połączenie z danymi odbywa się przy użyciu kontekstu zabezpieczeń konta procesu programu SQL Server (konta używanego przez usługę aparatu bazy danych programu SQL Server).

Aby pomyślnie odczytać dane źródłowe, musisz przyznać kontu używanemu przez silnik bazy danych SQL Server dostęp do danych źródłowych. Natomiast jeśli użytkownik programu SQL Server loguje się przy użyciu uwierzytelniania systemu Windows, użytkownik może odczytywać tylko te pliki, do których można uzyskać dostęp przy użyciu konta użytkownika, niezależnie od profilu zabezpieczeń procesu programu SQL Server.

Rozważmy na przykład użytkownika, który zalogował się do instancji SQL Server przy użyciu uwierzytelniania Windows. Aby użytkownik mógł użyć funkcji BULK INSERT lub OPENROWSET do zaimportowania danych z pliku danych do tabeli programu SQL Server, konto użytkownika wymaga dostępu do odczytu do pliku danych. Mając dostęp do pliku danych, użytkownik może zaimportować dane z pliku do tabeli, nawet jeśli proces programu SQL Server nie ma uprawnień dostępu do pliku. Użytkownik nie musi udzielać uprawnień dostępu do plików procesowi programu SQL Server.

Program SQL Server i system Microsoft Windows można skonfigurować tak, aby umożliwić wystąpieniu programu SQL Server łączenie się z innym wystąpieniem programu SQL Server przez przekazanie poświadczeń uwierzytelnionego użytkownika systemu Windows. Ten układ jest znany jako podszywanie się lub delegowanie . Zrozumienie, jak wersja programu SQL Server obsługuje zabezpieczenia personifikacji użytkownika, jest ważne w przypadku używania funkcji BULK INSERT lub OPENROWSET. Personifikacja użytkownika umożliwia, aby plik danych znajdował się na innym komputerze niż proces programu SQL Server lub użytkownik. Jeśli na przykład użytkownik na Computer_A ma dostęp do pliku danych w Computer_B, a delegowanie poświadczeń zostało ustawione odpowiednio, użytkownik może nawiązać połączenie z wystąpieniem programu SQL Server uruchomionym w Computer_C, uzyskać dostęp do pliku danych w Computer_B, i zbiorcze importowanie danych z tego pliku do tabeli w Computer_C.

Zbiorcze importowanie do programu SQL Server z pliku danych zdalnych

Aby używać BULK INSERT lub INSERT...SELECT * FROM OPENROWSET(BULK...) do zbiorczego importowania danych z innego komputera, plik danych musi być współdzielony między obiema komputerami. Aby określić udostępniony plik danych, użyj jego uniwersalnej nazwy konwencji nazewnictwa (UNC), która przyjmuje formę ogólną. \\Servername\Sharename\Path\Filename

Ponadto konto używane do uzyskiwania dostępu do pliku danych musi mieć uprawnienia wymagane do odczytu pliku na dysku zdalnym.

Na przykład następująca instrukcja BULK INSERT zbiorczo importuje dane do tabeli SalesOrderDetail bazy danych AdventureWorks z pliku danych o nazwie newdata.txt. Ten plik danych znajduje się w folderze udostępnionym o nazwie \dailyorders w katalogu udziału sieciowego o nazwie salesforce w systemie o nazwie computer2.

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

Notatka

To ograniczenie nie ma zastosowania do bcp narzędzia, ponieważ klient odczytuje plik niezależnie od programu SQL Server.

Zbiorcze importowanie z usługi Azure Blob Storage

Podczas importowania z usługi Azure Blob Storage, gdy dane nie są publiczne (anonimowy dostęp), utwórz CREATE DATABASE SCOPED CREDENTIAL na podstawie klucza SAS zaszyfrowanego przy użyciu polecenia CREATE MASTER KEY, a następnie utwórz zewnętrzne źródło bazy danych do wykorzystania w poleceniu BULK INSERT.

Alternatywnie, utwórz poświadczenie CREATE DATABASE SCOPED w oparciu o MANAGED IDENTITY, aby autoryzować żądania dostępu do danych na kontach magazynowych o ograniczonym dostępie. W przypadku korzystania z MANAGED IDENTITY, usługa Azure Storage musi udzielić uprawnień tożsamości zarządzanej wystąpienia, dodając wbudowaną rolę Współautora Danych Obiektu Blob w Storage. Ta rola dostępu opartego na rolach (RBAC) zapewnia dostęp do odczytu/zapisu tożsamości zarządzanej, zapewniając dostęp do niezbędnych kontenerów usługi Azure Blob Storage. Usługa Azure SQL Managed Instance ma przypisaną przez system tożsamość zarządzaną i może również mieć co najmniej jedną tożsamość zarządzaną przypisaną przez użytkownika. Do autoryzowania żądań można użyć tożsamości zarządzanych przypisanych przez system lub tożsamości zarządzanych przypisanych przez użytkownika. W przypadku autoryzacji zostanie użyta tożsamość default wystąpienia zarządzanego (tj. podstawowa tożsamość zarządzana przypisana przez użytkownika lub tożsamość zarządzana przypisana przez system, jeśli nie określono tożsamości zarządzanej przypisanej przez użytkownika).

Ważny

Tożsamość zarządzana nie jest obsługiwana w wersjach programu SQL Server przed programem SQL Server 2025.

Notatka

Nie używaj jawnej transakcji, bo w przeciwnym razie pojawi się błąd 4861.

Używanie funkcji BULK INSERT

W poniższym przykładzie pokazano, jak za pomocą polecenia BULK INSERT załadować dane z pliku CSV w lokalizacji magazynu obiektów blob platformy Azure, na której utworzono klucz sygnatury dostępu współdzielonego. Lokalizacja usługi Azure Blob Storage jest skonfigurowana jako zewnętrzne źródło danych. Wymaga to poświadczenia o zakresie bazy danych przy użyciu sygnatury dostępu współdzielonego szyfrowanej przy użyciu klucza głównego w bazie danych użytkownika.

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

W poniższym przykładzie pokazano, jak za pomocą BULK INSERT polecenia załadować dane z pliku CSV w lokalizacji usługi Azure Blob Storage przy użyciu tożsamości zarządzanej. Lokalizacja usługi Azure Blob Storage jest skonfigurowana jako zewnętrzne źródło danych.

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

Ważny

Usługa Azure SQL Database nie obsługuje odczytu z plików systemu Windows.

Korzystanie z zestawu OPENROWSET

W poniższym przykładzie pokazano, jak za pomocą polecenia OPENROWSET załadować dane z pliku CSV w lokalizacji magazynu obiektów blob platformy Azure, na której utworzono klucz sygnatury dostępu współdzielonego. Lokalizacja usługi Azure Blob Storage jest skonfigurowana jako zewnętrzne źródło danych. Wymaga to poświadczenia o zakresie bazy danych przy użyciu sygnatury dostępu współdzielonego szyfrowanej przy użyciu klucza głównego w bazie danych użytkownika.

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

Ważny

Usługa Azure SQL Database nie obsługuje odczytu z plików systemu Windows.

Powiązana zawartość