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

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance

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

BULK INSERT ステートメント

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

BULK INSERT の例

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

OPENROWSET(BULK...) 関数

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

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

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

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

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

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

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

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

ユーザーが 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 上のデータ ファイルにアクセスでき、資格情報の委任が適切に設定されている場合、このユーザーは、Computer_C 上で実行されている SQL Server のインスタンスに接続して、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 AdventureWorks2022.Sales.SalesOrderDetail
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';

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

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

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

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

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;

関連するコンテンツ

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