データの一括インポートでのフォーマット ファイルの使用 (SQL Server)Use a Format File to Bulk Import Data (SQL Server)

適用対象: ○SQL Server ○Azure SQL Database ○Azure SQL Data Warehouse ○Parallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

このトピックでは、一括インポート操作でのフォーマット ファイルの使用方法について説明します。This topic illustrates the use of a format file in bulk-import operations. フォーマット ファイルでは、データ ファイルのフィールドがテーブルの列にマップされます。A format file maps the fields of the data file to the columns of the table. 詳細については、「 フォーマット ファイルの作成 (SQL Server) 」を参照してください。Please review Create a Format File (SQL Server) for additional information.

[外枠]Outline
はじめにBefore You Begin
テスト条件の例Example Test Conditions
 ● サンプル テーブル ● Sample Table
 ● サンプル データ ファイル ● Sample Data File
フォーマット ファイルの作成Creating the Format Files
 ● XML 以外のフォーマット ファイルの作成 ● Creating a Non-XML Format File
 ● XML フォーマット ファイルの作成 ● Creating an XML Format File
フォーマット ファイルを使用してデータを一括インポートUsing a Format File to Bulk Import Data
 ● bcp と XML 以外のフォーマット ファイルの使用 ● Using bcp and Non-XML Format File
 ● bcp と XML フォーマット ファイルの使用 ● Using bcp and XML Format File
 ● BULK INSERT と XML 以外のフォーマット ファイルの使用 ● Using BULK INSERT and Non-XML Format File
 ● BULK INSERT と XML フォーマット ファイルの使用 ● Using BULK INSERT and XML Format File
 ● OPENROWSET(BULK...) と XML 以外のフォーマット ファイルの使用 ● Using OPENROWSET(BULK...) and Non-XML Format File
 ● OPENROWSET(BULK...) と XML フォーマット ファイルの使用 ● Using OPENROWSET(BULK...) and XML Format File

はじめにBefore You Begin

  • Unicode 文字データ ファイルを操作するフォーマット ファイルの場合、すべての入力フィールドが Unicode テキスト文字列 (つまり、固定サイズの Unicode 文字列または終端文字が指定された Unicode 文字列) でなければなりません。For a format file to work with a Unicode character data file, all input fields must be Unicode text strings (that is, either fixed-size or character-terminated Unicode strings).
  • SQLXML データを一括エクスポートまたは一括インポートする場合、フォーマット ファイルのデータ型には次のいずれかを使用します。To bulk export or import SQLXML data, use one of the following data types in your format file:
    • SQLCHAR または SQLVARYCHAR (データは、クライアント コード ページまたは照合順序で暗黙的に指定されるコード ページで送られます)SQLCHAR or SQLVARYCHAR (the data is sent in the client code page or in the code page implied by the collation)
    • SQLNCHAR または SQLNVARCHAR (データは Unicode として送信されます)SQLNCHAR or SQLNVARCHAR (the data is sent as Unicode)
    • SQLBINARY または SQLVARYBIN (データは変換なしで送られます)SQLBINARY or SQLVARYBIN (the data is sent without any conversion).
  • Azure SQL Database と Azure SQL Data Warehouse は、 bcpのみをサポートします。Azure SQL Database and Azure SQL Data Warehouse only support bcp. その他の詳細については、以下を参照してください。For additional information, see:

テスト条件の例Example Test Conditions

このトピックのフォーマット ファイルの例は、以下に定義されたテーブルとデータ ファイルに基づいています。The examples of format files in this topic are based on the table and data file defined below.

サンプル テーブルSample Table

以下のスクリプトでは、テスト データベースと myFirstImportという名前のテーブルが作成されます。The script below creates a test database and a table named myFirstImport. Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.MyFirstImport (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );

サンプル データ ファイルSample Data File

メモ帳を使用して、空のファイル D:\BCP\myFirstImport.bcp を作成し、次のデータを挿入します。Using Notepad, create an empty file D:\BCP\myFirstImport.bcp and insert the following data:

1,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
3,Stella,Rosenhain,1992-03-02

また、次の PowerShell スクリプトを実行して、データ ファイルを作成および設定することもできます。Alternatively, you can execute the following PowerShell script to create and populate the data file:

Clear-Host
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = Join-Path -Path $dir -ChildPath 'MyFirstImport.bcp';

# Confirm directory exists
IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# clear content, will error if file does not exist, can be ignored
Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data
Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,1992-03-02';

#Review content
Get-Content -Path $bcpFile;
Notepad.exe $bcpfile;

フォーマット ファイルの作成Creating the Format Files

SQL Server は、非 XML 形式と XML 形式の 2 種類のフォーマット ファイルをサポートしています。SQL Server support two types of format file: non-XML format and XML format. XML 以外のフォーマットとは、以前のバージョンの SQL Server でサポートされる従来のフォーマットです。The non-XML format is the original format that is supported by earlier versions of SQL Server.

XML 以外のフォーマット ファイルの作成Creating a Non-XML Format File

詳細については、「 XML 以外のフォーマット ファイル (SQL Server) 」を参照してください。Please review Non-XML Format Files (SQL Server) for detailed information. 次のコマンドでは、 bcp ユーティリティ を使用し、 myFirstImport.fmtのスキーマに基づいて XML 以外のフォーマット ファイル myFirstImportを生成します。The following command will use the bcp utility to generate a non-xml format file, myFirstImport.fmt, based on the schema of myFirstImport. bcp コマンドを使用してフォーマット ファイルを作成するには、 format 引数を指定し、データ ファイルのパスの代わりに nul を使用します。To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. format オプションには、次に示す -f オプションが必要です。The format option also requires the -f option. さらに、この例では、修飾子 c を使用して文字データを指定し、 t を使用して フィールド ターミネータとしてコンマを指定し、 T を使用して統合セキュリティによる信頼された接続を指定します。In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

bcp TestDatabase.dbo.myFirstImport format nul -c -f D:\BCP\myFirstImport.fmt -t, -T

REM Review file
Notepad D:\BCP\myFirstImport.fmt

非 XML フォーマット ファイルの D:\BCP\myFirstImport.fmt は次のようになります。Your non-XML format file, D:\BCP\myFirstImport.fmt should look as follows:

13.0
4
1       SQLCHAR             0       7       ","      1     PersonID               ""
2       SQLCHAR             0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       11      "\r\n"   4     BirthDate              ""

重要

XML 以外のフォーマット ファイルは、キャリッジ リターン\ライン フィードで終わるようにします。Ensure your non-XML format file ends with a carriage return\line feed. そうしないと、次のエラー メッセージが発生する可能性があります。Otherwise you will likely receive the following error message:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

XML フォーマット ファイルの作成Creating an XML Format File

詳細については、「 XML フォーマット ファイル (SQL Server) 」を参照してください。Please review XML Format Files (SQL Server) for detailed information. 次のコマンドでは、 bcp ユーティリティ を使用し、 myFirstImport.xmlのスキーマに基づいて XML のフォーマット ファイル myFirstImportを生成します。The following command will use the bcp utility to create an xml format file, myFirstImport.xml, based on the schema of myFirstImport. bcp コマンドを使用してフォーマット ファイルを作成するには、 format 引数を指定し、データ ファイルのパスの代わりに nul を使用します。To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. format オプションには常に -f オプションが必要です。XML フォーマット ファイルを作成するには、 -x オプションも指定する必要があります。The format option always requires the -f option, and to create an XML format file, you must also specify the -x option. さらに、この例では、修飾子 c を使用して文字データを指定し、 t を使用して フィールド ターミネータとしてコンマを指定し、 T を使用して統合セキュリティによる信頼された接続を指定します。In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

bcp TestDatabase.dbo.myFirstImport format nul -c -x -f D:\BCP\myFirstImport.xml -t, -T

REM Review file
Notepad D:\BCP\myFirstImport.xml

XML フォーマット ファイルの D:\BCP\myFirstImport.xml は次のようになります。Your XML format file, D:\BCP\myFirstImport.xml should look as follows:

<?xml version="1.0"?>
<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="11"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
 </ROW>
</BCPFORMAT>

フォーマット ファイルを使用してデータを一括インポートUsing a Format File to Bulk Import Data

次の例では、データベース、データ ファイル、および上記で作成したフォーマット ファイルを使用します。The examples below use the database, datafile, and format files created above.

bcpXML 以外のフォーマット ファイルの使用Using bcp and Non-XML Format File

コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport"

bcpXML フォーマット ファイルの使用Using bcp and XML Format File

コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.xml -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport;"

BULK INSERTXML 以外のフォーマット ファイルの使用Using BULK INSERT and Non-XML Format File

Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport   
   FROM 'D:\BCP\myFirstImport.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.fmt');  
GO  

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

BULK INSERTXML フォーマット ファイルの使用Using BULK INSERT and XML Format File

Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport   
   FROM 'D:\BCP\myFirstImport.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.xml');  
GO  

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

OPENROWSET(BULK...)XML 以外のフォーマット ファイルの使用Using OPENROWSET(BULK...) and Non-XML Format File

Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

OPENROWSET(BULK...)XML フォーマット ファイルの使用Using OPENROWSET(BULK...) and XML Format File

Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport 
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

その他の例More examples!

フォーマット ファイルの作成 (SQL Server)Create a Format File (SQL Server)
フォーマット ファイルを使用したテーブル列のスキップ (SQL Server)Use a Format File to Skip a Table Column (SQL Server)
フォーマット ファイルを使用したデータ フィールドのスキップ (SQL Server)Use a Format File to Skip a Data Field (SQL Server)
フォーマット ファイルを使用したテーブル列とデータ ファイル フィールドのマッピング (SQL Server)Use a Format File to Map Table Columns to Data-File Fields (SQL Server)

参照See also

bcp ユーティリティ bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
XML 以外のフォーマット ファイル (SQL Server) Non-XML Format Files (SQL Server)
XML フォーマット ファイル (SQL Server)XML Format Files (SQL Server)
データのインポートまたはエクスポート用のフォーマット ファイル (SQL Server)Format Files for Importing or Exporting Data (SQL Server)