BULK INSERT または OPENROWSET(BULK...) を使用して SQL Server にデータをインポートする

適用対象:yesSQL Server (サポートされているすべてのバージョン) Yes Azure SQL Database Yes Azure SQL Managed Instance

この記事では、SQL transact-sql の BULK INSERT ステートメントと INSERT... の使用方法の概要について説明します。select * from OPENROWSET (BULK...) ステートメントを選択すると、データファイルから SQL Server または Azure SQL Database テーブルにデータを一括インポートできます。 また、BULK INSERT および OPENROWSET(BULK...) を使用する場合のセキュリティの注意点や、リモート データ ソースから一括インポートする方法についても説明します。

Note

BULK INSERT または OPENROWSET (BULK...) を使用する場合、SQL Server バージョンが偽装を処理する方法を理解することが重要です。 詳細については、後の「セキュリティの注意点」を参照してください。

BULK INSERT ステートメント

BULK INSERT では、データ ファイルからテーブルにデータが読み込まれます。 この機能は、 bcpコマンドのinオプションによって提供される機能に似ています。ただし、データファイルは SQL Server プロセスによって読み取られます。 BULK INSERT 構文の説明については、「 BULK INSERT (transact-sql SQL)」を参照してください。

BULK INSERT の例

OPENROWSET(BULK...)機能

OPENROWSET 一括行セット プロバイダーには、BULK オプションを指定して OPENROWSET 関数を呼び出すことによってアクセスします。 OPENROWSET(BULK...) 関数では、OLE DB プロバイダー経由でデータ ファイルなどのリモート データ ソースに接続することで、リモート データにアクセスできます。

データを一括インポートするには、INSERT ステートメントの SELECT...FROM 句から OPENROWSET(BULK...) を呼び出します。 データの一括インポートの基本構文は次のとおりです。

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

OPENROWSET(BULK...) を INSERT ステートメント内で使用する場合は、テーブル ヒントがサポートされます。 TABLOCK などの通常のテーブル ヒントに加えて、BULK 句では、次の特殊なテーブル ヒントを使用できます:IGNORE_CONSTRAINTS (CHECK 制約のみ無視します)、IGNORE_TRIGGERS、KEEPDEFAULTS、KEEPIDENTITY。 詳細については、「テーブルヒント (transact-sql SQL)」を参照してください。

BULK オプションのその他の使用方法については、「 OPENROWSET (transact-sql SQL)」を参照してください。

INSERT...SELECT * FROM OPENROWSET(BULK...) ステートメント - 例:

セキュリティに関する考慮事項

ユーザーが SQL Server ログインを使用している場合は、SQL Server プロセスアカウントのセキュリティプロファイルが使用されます。 SQL Server 認証を使用したログインは、データベース エンジン以外では認証できません。 そのため、SQL Server 認証を使用したログインによって BULK INSERT コマンドが開始されると、SQL Server プロセス アカウント (SQL Server データベース エンジン サービスで使用されるアカウント) のセキュリティ コンテキストを使用してデータへの接続が行われます。

ソース データを正しく読み取るには、SQL Server データベース エンジンで使用されるアカウントに対して、ソース データへのアクセス権を付与する必要があります。 これに対し、SQL Server ユーザーが Windows 認証を使用してログオンした場合、ユーザーは SQL Server プロセスのセキュリティプロファイルに関係なく、ユーザーアカウントがアクセスできるファイルのみを読み取ることができます。

たとえば、Windows 認証を使用して SQL Server のインスタンスにログインしたユーザーを考えてみます。 ユーザーが BULK INSERT または OPENROWSET を使用してデータファイルから SQL Server テーブルにデータをインポートできるようにするには、ユーザーアカウントにデータファイルへの読み取りアクセス権が必要です。 データファイルへのアクセスを使用すると、SQL Server プロセスにファイルへのアクセス許可がない場合でも、ユーザーはファイルからテーブルにデータをインポートできます。 ユーザーは、SQL Server プロセスにファイルアクセス許可を与える必要はありません。

SQL Server と Microsoft Windows を構成して、認証された Windows ユーザーの資格情報を転送することによって、SQL Server のインスタンスが SQL Server の別のインスタンスに接続できるようにすることができます。 この設定を、" 権限借用 " または " 権限委譲" といいます。 BULK INSERT または OPENROWSET を使用する場合、ユーザーの偽装の SQL Server バージョンハンドルセキュリティを理解することが重要です。 ユーザー偽装を使用すると、データファイルを SQL Server プロセスまたはユーザーとは別のコンピューターに配置できます。 たとえば、 Computer_AのユーザーがComputer_B上のデータファイルにアクセスし、資格情報の委任が適切に設定されている場合、ユーザーはComputer_Cで実行されている SQL Server のインスタンスに接続し、 Computer_B上のデータファイルにアクセスして、そのファイルからComputer_C上のテーブルにデータを一括インポートできます。

リモート データ ファイルから SQL Server への一括インポート

BULK INSERT または INSERT...SELECT * FROM OPENROWSET(BULK...) を使用して別のコンピューターからデータを一括インポートするには、データ ファイルを 2 台のコンピューター間で共有している必要があります。 共有データ ファイルを指定するには、UNC (汎用名前付け規則) 名を使用します。UNC 名の一般的な形式は、 \\\\\\\\\\です。 また、データ ファイルへのアクセスに使用されるアカウントは、リモート ディスク上のファイルの読み取りに必要な権限を持っている必要があります。

たとえば、次の BULK INSERT ステートメントでは、 SalesOrderDetail というデータ ファイルから AdventureWorks データベースの newdata.txtテーブルにデータの一括インポートを行います。 このデータ ファイルは、 \dailyorders というシステムの salesforce というネットワーク共有ディレクトリの computer2という共有フォルダーにあります。

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

Note

クライアントは SQL Server とは無関係にファイルを読み取るため、この制限はbcpユーティリティには適用されません。

Azure Blob Storage からの一括インポート

Azure Blob Storage からパブリック (匿名アクセス) ではないデータをインポートするときは、MASTER KEY で暗号化された SAS キーに基づいて DATABASE SCOPED CREDENTIAL を作成した後、BULK INSERT コマンドで使用する外部データベース ソースを作成します。

Note

明示的なトランザクションを使用しないでください。そうしないと、4861 エラーが発生します。

BULK INSERT の使用

次の例では、BULK INSERT コマンドを使用して、SAS キーを作成した Azure Blob Storage の場所にある csv ファイルからデータを読み込む方法を示します。 Azure Blob Storage の場所は、外部データ ソースとして構成されます。 これには、ユーザー データベース内でマスター キーを使用して暗号化された共有アクセス署名を使用するデータベース スコープ資格情報が必要です。

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

重要

Azure SQL Database では、Windows ファイルからの読み取りはサポートされません。

OPENROWSET の使用

次の例は、OPENROWSET コマンドを使用して、SAS キーを作成した Azure Blob Storage の場所にある csv ファイルからデータを読み込む方法を示しています。 Azure Blob Storage の場所は、外部データ ソースとして構成されます。 これには、ユーザー データベース内でマスター キーを使用して暗号化された共有アクセス署名を使用するデータベース スコープ資格情報が必要です。

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

重要

Azure SQL Database では、Windows ファイルからの読み取りはサポートされません。

関連項目