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

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

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

注意

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

BULK INSERT ステートメント

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

BULK INSERT の例

• BULK INSERT (Transact-SQL)
• XML ドキュメントの一括インポートと一括エクスポートの例 (SQL Server)
• データの一括インポート時の ID 値の保持 (SQL Server)
• 一括インポート中の NULL の保持または既定値の使用 (SQL Server)
• フィールド ターミネータと行ターミネータの指定 (SQL Server)
• データの一括インポートでのフォーマット ファイルの使用 (SQL Server)
• 文字形式を使用したデータのインポートまたはエクスポート (SQL Server)
• ネイティブ形式を使用したデータのインポートまたはエクスポート (SQL Server)
• Unicode 文字形式を使用したデータのインポートまたはエクスポート (SQL Server)
• Unicode ネイティブ形式を使用したデータのインポートまたはエクスポート (SQL Server)
• フォーマット ファイルを使用したテーブル列のスキップ (SQL Server)
• フォーマット ファイルを使用したテーブル列とデータ ファイル フィールドのマッピング (SQL Server)

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)」を参照してください。

BULK オプションの上記以外の使い方の詳細については、「OPENROWSET (Transact-SQL)」を参照してください。

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

• XML ドキュメントの一括インポートと一括エクスポートの例 (SQL Server)
• データの一括インポート時の ID 値の保持 (SQL Server)
• 一括インポート中の NULL の保持または既定値の使用 (SQL Server)
• データの一括インポートでのフォーマット ファイルの使用 (SQL Server)
• 文字形式を使用したデータのインポートまたはエクスポート (SQL Server)
• フォーマット ファイルを使用したテーブル列のスキップ (SQL Server)
• フォーマット ファイルを使用したデータ フィールドのスキップ (SQL Server)
• フォーマット ファイルを使用したテーブル列とデータ ファイル フィールドのマッピング (SQL Server)

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

ユーザーが SQL Server ログインを使用する場合は、 SQL Server プロセス アカウントのセキュリティ プロファイルが使用されます。 SQL Server 認証を使用したログインは、データベース エンジン以外では認証できません。 そのため、SQL Server 認証を使用したログインによって BULK INSERT コマンドが開始されると、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 では、認証されている Windows ユーザーの資格情報を転送することで、 SQL Server インスタンスから別の SQL Server インスタンスへ接続できるように構成することが可能です。 この設定を、" 権限借用 " または " 権限委譲" といいます。 BULK INSERT または OPENROWSET を使用する場合は、 SQL Server バージョンによってユーザーの権限借用のセキュリティがどのように処理されるかを理解しておくことが重要です。 ユーザーの権限を借用することで、 SQL Server プロセスまたはユーザーのいずれかが使用しているコンピューターとは異なるコンピューターにデータ ファイルを常駐させることができます。 たとえば、 Computer_A 上のユーザーが Computer_B上のデータ ファイルにアクセスでき、資格情報の委任が適切に設定されている場合、このユーザーは、 SQL Server Computer_C 上で実行されているのインスタンスに接続して、 Computer_B上のデータ ファイルにアクセスし、そのファイルから Computer_C上のテーブルにデータを一括インポートできます。

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

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

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

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

注意

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

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

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

または、 に基づいて MANAGED IDENTITYDATABASE SCOPED CREDENTIAL を作成して、非パブリック ストレージ アカウントでのデータ アクセスの要求を承認します。 を使用MANAGED IDENTITYする場合、Azure Storage は、必要なAzure Blob Storage コンテナーのマネージド ID への読み取り/書き込みアクセスを提供する、ストレージ BLOB データ共同作成者組み込みの Azure ロールベースのアクセス制御 (RBAC) ロールを追加することで、インスタンスのマネージド ID にアクセス許可を付与する必要があります。 Azure SQL Managed Instanceシステム割り当てマネージド ID があり、1 つ以上のユーザー割り当てマネージド ID を持つことができます。 システム割り当てマネージド ID またはユーザー割り当てマネージド ID のいずれかを使って要求を認可できます。 承認には、 default マネージド インスタンスの ID が使用されます (ユーザー割り当てマネージド ID が指定されていない場合はプライマリ ユーザー割り当てマネージド ID、またはシステム割り当てマネージド ID)。

重要

マネージド ID は、Azure SQLにのみ適用されます。 SQL Serverはマネージド ID をサポートしていません。

注意

明示的なトランザクションを使用しないでください。そうしないと、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');

次の例は、BULK INSERT コマンドを使用して、マネージド ID を使用して 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 = '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');

重要

マネージド ID は、Azure SQLにのみ適用されます。 SQL Serverはマネージド ID をサポートしていません。

重要

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 ファイルからの読み取りはサポートされません。

関連項目

• INSERT (Transact-SQL)
• SELECT 句 (Transact-SQL)
• データの一括インポートと一括エクスポート (SQL Server)
• OPENROWSET (Transact-SQL)
• SELECT (Transact-SQL)
• FROM (Transact-SQL)
• bcp ユーティリティ
• BULK INSERT (Transact-SQL)