Поделиться через

Импорт данных в SQL Server при помощи инструкции BULK INSERT или OPENROWSET(BULK...)

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

В этой статье представлен обзор использования инструкции Transact-SQL BULK INSERT и INSERT...SELECT * FROM OPENROWSET(BULK...) инструкции для массового импорта данных из файла данных в таблицу SQL Server или Базы данных SQL Azure.

В этой статье также описываются рекомендации по безопасности использования BULK INSERT и OPENROWSET(BULK...)использования этих методов для массового импорта из удаленного источника данных.

Инструкция BULK INSERT

BULK INSERT загружает данные из файла данных в таблицу. Эта функция аналогична той, которую предоставляет параметр in команды bcp. Однако файл данных считывается процессом SQL Server. Описание синтаксиса BULK INSERT см. в разделе BULK INSERT (Transact-SQL).

Примеры BULK INSERT

Функция OPENROWSET(BULK...)

Доступ к поставщику массового набора строк осуществляется путем вызова функции OPENROWSET и указания опции OPENROWSET. Эта OPENROWSET(BULK...) функция позволяет получить доступ к удаленным данным путем подключения к удаленному источнику данных, например к файлу данных через поставщика данных.

Чтобы выполнить массовый импорт данных, вызовите OPENROWSET(BULK...) из предложения SELECT...FROM, находящегося в инструкции INSERT.

Основной синтаксис массового импорта данных:

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

При использовании в инструкции INSERT, OPENROWSET(BULK...) поддерживает табличные подсказки. Помимо регулярных указаний таблицы, таких как TABLOCK, оператор BULK может принимать следующие специализированные указания таблицы:

  • IGNORE_CONSTRAINTS (игнорирует только ограничения CHECK)
  • IGNORE_TRIGGERS
  • KEEPDEFAULTS
  • KEEPIDENTITY

Дополнительные сведения см. в указаниях таблицы (Transact-SQL).

Дополнительные сведения об использовании параметра BULK см. в статье OPENROWSET BULK (Transact-SQL).

ВСТАВКА... ИНСТРУКЦИИ SELECT * FROM OPENROWSET(BULK...) — примеры

Вопросы безопасности

Если пользователь входит в систему с помощью имени входа SQL Server, используется профиль безопасности учетной записи процесса SQL Server. Вход с проверкой подлинности SQL Server не может быть аутентифицирован за пределами механизма базы данных. Поэтому при инициации команды BULK INSERT при входе в систему с использованием проверки подлинности SQL Server подключение к данным выполняется с помощью контекста безопасности учетной записи процесса SQL Server (учетная запись, используемая службой ядра базы данных SQL Server).

Чтобы успешно считывать исходные данные, необходимо предоставить учетной записи, используемой ядром СУБД SQL Server, доступ к этим исходным данным. В отличие от этого, если пользователь SQL Server входит в систему с помощью проверки подлинности Windows, пользователь может читать только те файлы, к которым можно получить доступ учетной записи пользователя, независимо от профиля безопасности процесса SQL Server.

Например, рассмотрим пользователя, вошедшего в экземпляр SQL Server с помощью проверки подлинности Windows. Чтобы пользователь мог использовать BULK INSERT или OPENROWSET для импорта данных из файла данных в таблицу SQL Server, для учетной записи пользователя требуется доступ на чтение к файлу данных. При доступе к файлу данных пользователь может импортировать данные из файла в таблицу, даже если у процесса SQL Server нет разрешения на доступ к файлу. Пользователю не нужно предоставлять разрешение на доступ к файлам процессу SQL Server.

SQL Server и Microsoft Windows можно настроить так, чтобы экземпляр SQL Server мог подключаться к другому экземпляру SQL Server, пересылая учетные данные аутентифицированного пользователя Windows. Такой подход называется олицетворением или делегированием. Понимание того, как разные версии SQL Server управляют безопасностью при олицетворении пользователей, важно при использовании BULK INSERT или OPENROWSET. Олицетворение пользователя позволяет файлу данных находиться на другом компьютере, отличном от процесса SQL Server или пользователя. Например, если пользователь на Computer_A имеет доступ к файлу данных Computer_B, а делегирование учетных данных задано соответствующим образом, пользователь может подключиться к экземпляру SQL Server, работающему на Computer_C, получить доступ к файлу данных Computer_B и массово импортировать данные из этого файла в таблицу Computer_C.

Массовый импорт в SQL Server из удаленного файла данных

Для массового импорта данных с другого компьютера с помощью BULK INSERT или INSERT...SELECT * FROM OPENROWSET(BULK...), файл данных должен быть доступен на обоих компьютерах. Чтобы указать общий файл данных, используйте его универсальное соглашение об именовании (UNC), которое принимает общую форму. \\Servername\Sharename\Path\Filename

Кроме того, используемая учетная запись должна обладать разрешениями, необходимыми для чтения этого файла на удаленном диске.

Например, инструкция BULK INSERT производит массовый импорт в таблицу SalesOrderDetail базы данных AdventureWorks из файла данных с именем newdata.txt. Этот файл данных находится в общей папке \dailyorders, расположенной в общем сетевом каталоге salesforce компьютера с именем computer2.

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

Примечание.

Это ограничение не применяется к служебной bcp программе, так как клиент считывает файл независимо от SQL Server.

Массовый импорт данных из хранилища объектов BLOB Azure

При импорте из хранилища BLOB-объектов Azure, если данные не являются общедоступными (анонимный доступ отсутствует), создайте CREATE DATABASE SCOPED учетные данные на основе ключа SAS, зашифрованного с помощью CREATE MASTER KEY, а затем создайте внешний источник данных для использования в вашей BULK INSERT команде.

Кроме того, создайте учетные данные CREATE DATABASE SCOPED CREDENTIAL на основе MANAGED IDENTITY чтобы авторизовать запросы на доступ к данным в непубличных учетных записях хранения. При использовании MANAGED IDENTITY хранилище Azure должно предоставить разрешения управляемому удостоверению экземпляра, добавив встроенную роль Storage Blob Data Contributor управления доступом на основе ролей Azure (RBAC), которая предоставляет доступ на чтение и запись к управляемому удостоверению для необходимых контейнеров хранилища BLOB-объектов Azure. Управляемый экземпляр SQL Azure имеет системно назначенное управляемое удостоверение и также может иметь одно или несколько управляемых удостоверений, назначаемых пользователем. Для авторизации запросов можно использовать управляемые удостоверения, назначаемые системой или пользователем. Для авторизации default будет использоваться удостоверение управляемого экземпляра (это основное управляемое удостоверение, назначаемое пользователем, или управляемое удостоверение, назначаемое системой, если назначаемое пользователем управляемое удостоверение не указано).

Внимание

Управляемое удостоверение не поддерживается в версиях SQL Server до SQL Server 2025.

Примечание.

Не используйте явную транзакцию, чтобы не получить ошибку 4861.

Используйте BULK INSERT

В следующем примере показано, как с помощью BULK INSERT команды загрузить данные из CSV-файла в расположении хранилища BLOB-объектов Azure, в котором вы создали ключ SAS. Расположение хранилища BLOB-объектов Azure настроено как внешний источник данных. Для этого требуются учетные данные базы данных с ограниченной областью действия с использованием общей подписи доступа, зашифрованной с помощью основного ключа в пользовательской базе данных.

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

В следующем примере показано, как использовать команду BULK INSERT для загрузки данных из CSV-файла в хранилище BLOB-объектов Azure с помощью управляемой идентичности. Расположение хранилища BLOB-объектов Azure настроено как внешний источник данных.

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

Внимание

База данных SQL Azure не поддерживает чтение данных из файлов Windows.

Использование OPENROWSET

В следующем примере показано, как с помощью OPENROWSET команды загрузить данные из CSV-файла в расположении хранилища BLOB-объектов Azure, в котором вы создали ключ SAS. Расположение хранилища BLOB-объектов Azure настроено как внешний источник данных. Для этого требуются учетные данные базы данных с ограниченной областью действия с использованием общей подписи доступа, зашифрованной с помощью основного ключа в пользовательской базе данных.

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

Внимание

База данных SQL Azure не поддерживает чтение данных из файлов Windows.

Связанный контент