データの一括インポート時の ID 値の保持 (SQL Server)Keep Identity Values When Bulk Importing 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

ID 値を含んでいるデータ ファイルを Microsoft SQL Server のインスタンスに一括インポートできます。Data files that contain identity values can be bulk imported into an instance of Microsoft SQL Server. 既定では、インポートされたデータ ファイルの ID 列の値は無視され、 SQL ServerSQL Server によって固有の値が自動的に割り当てられます。By default, the values for the identity column in the data file that is imported are ignored and SQL ServerSQL Server assigns unique values automatically. 固有の値は、テーブル作成時に指定されたシード値と増分値に基づいています。The unique values are based on the seed and increment values that are specified during table creation.

データ ファイルにテーブルの ID 列の値が含まれていない場合は、フォーマット ファイルを使用してデータをインポートするときにテーブルの ID 列をスキップするように指定します。If the data file does not contain values for the identifier column in the table, use a format file to specify that the identifier column in the table should be skipped when importing data. 詳細については、「 フォーマット ファイルを使用したテーブル列のスキップ (SQL Server) 」を参照してください。See Use a Format File to Skip a Table Column (SQL Server) for additional information.

[外枠]Outline
ID 値を維持するKeep Identity Values
テスト条件の例Example Test Conditions
 ● サンプル テーブル ● Sample Table
 ● サンプル データ ファイル ● Sample Data File
 ● XML 形式以外のフォーマット ファイルのサンプル ● Sample Non-XML Format File
使用例Examples
 ● フォーマット ファイルなしで bcp を使用して ID 値を維持する方法 ● Using bcp and Keeping Identity Values without a Format File
 ● XML 形式以外のフォーマット ファイルで bcp を使用して ID 値を維持する方法 ● Using bcp and Keeping Identity Values with a Non-XML Format File
 ● フォーマット ファイルなしで bcp と生成される ID 値を使用する方法 ● Using bcp and Generated Identity Values without a Format File
 ● XML 形式以外のフォーマット ファイルで bcp と生成される ID 値を使用する方法 ● Using bcp and Generated Identity Values with a Non-XML Format File
 ● フォーマット ファイルなしで BULK INSERT を使用して ID 値を維持する方法 ● Using BULK INSERT and Keeping Identity Values without a Format File
 ● XML 形式以外のフォーマット ファイルで BULK INSERT を使用して ID 値を維持する方法 ● Using BULK INSERT and Keeping Identity Values with a Non-XML Format File
 ● フォーマット ファイルなしで BULK INSERT と生成される ID 値を使用する方法 ● Using BULK INSERT and Generated Identity Values without a Format File
 ● XML 形式以外のフォーマット ファイルで BULK INSERT と生成される ID 値を使用する方法 ● Using BULK INSERT and Generated Identity Values with a Non-XML Format File
 ● XML 形式以外のフォーマット ファイルで OPENROWSET を使用して ID 値を維持する方法 ● Using OPENROWSET and Keeping Identity Values with a Non-XML Format File
 ● XML 形式以外のフォーマット ファイルで OPENROWSET と生成される ID 値を使用する方法 ● Using OPENROWSET and Generated Identity Values with a Non-XML Format File

ID 値を維持する Keep Identity Values

テーブルにデータ行を一括インポートするときに SQL ServerSQL Server が ID 値を割り当てないようにするには、適切な keep-identity コマンド修飾子を使用します。To prevent SQL ServerSQL Server from assigning identity values while bulk importing data rows into a table, use the appropriate keep-identity command qualifier. keep-identity 修飾子を指定すると、 SQL ServerSQL Server ではデータ ファイルの ID 値を使用します。When you specify a keep-identity qualifier, SQL ServerSQL Server uses the identity values in the data file. このような修飾子は次のとおりです。These qualifiers are as follows:

コマンドCommand Keep-identity 修飾子Keep-identity qualifier 修飾子の種類Qualifier type
bcpbcp -E-E スイッチSwitch
BULK INSERTBULK INSERT KEEPIDENTITYKEEPIDENTITY 引数Argument
INSERT ...SELECT * FROM OPENROWSET(BULK...)INSERT ... SELECT * FROM OPENROWSET(BULK...) KEEPIDENTITYKEEPIDENTITY テーブル ヒントTable hint

詳細については、「bcp Utility」、「BULK INSERT (Transact-SQL)」、「OPENROWSET (Transact-SQL)」、「INSERT (Transact-SQL)」、「SELECT (Transact-SQL)」、「Table Hints (Transact-SQL)」を参照してください。For more information, see bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL), and Table Hints (Transact-SQL).

注意

複数のテーブルで使用できる自動的に増分する番号、またはテーブルを参照せずにアプリケーションから呼び出すことができる自動的に増分する番号を作成するには、「 シーケンス番号」を参照してください。To create an automatically incrementing number that can be used in multiple tables or that can be called from applications without referencing any table, see Sequence Numbers.

テスト条件の例Example Test Conditions

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

サンプル テーブルSample Table

以下のスクリプトでは、テスト データベースと myIdentityという名前のテーブルが作成されます。The script below creates a test database and a table named myIdentity. 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.myIdentity ( 
   PersonID smallint IDENTITY(1,1) NOT NULL,
   FirstName varchar(25) NOT NULL,
   LastName varchar(30) NOT NULL,
   BirthDate date
   );

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

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

3,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
1,Stella,Rosenhain,1992-03-02
4,Miller,Dylan,1954-01-05

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

cls
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = $dir + 'myIdentity.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 '3,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '1,Stella,Rosenhain,1992-03-02';
Add-Content -Path $bcpFile -Value '4,Miller,Dylan,1954-01-05';

#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;

XML 形式以外のフォーマット ファイルのサンプルSample Non-XML Format File

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 以外のフォーマット ファイル (SQL Server) 」を参照してください。Please review Non-XML Format Files (SQL Server) for detailed information. 次のコマンドでは、 bcp ユーティリティ を使用し、 myIdentity.fmtのスキーマに基づいて XML 以外のフォーマット ファイル myIdentityを生成します。The following command will use the bcp utility to generate a non-xml format file, myIdentity.fmt, based on the schema of myIdentity. 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.myIdentity format nul -c -f D:\BCP\myIdentity.fmt -t, -T

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

重要

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

使用例Examples

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

フォーマット ファイルなしで bcp を使用して ID 値を維持する方法Using bcp and Keeping Identity Values without a Format File

-E スイッチ。-E switch. コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

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

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t, -E

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

XML 形式以外のフォーマット ファイルbcp を使用して ID 値を維持する方法Using bcp and Keeping Identity Values with a Non-XML Format File

-E スイッチと -f スイッチ。-E and -f switches. コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

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

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

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

フォーマット ファイルなしで bcp と生成される ID 値を使用する方法Using bcp and Generated Identity Values without a Format File

既定値を使用します。Using defaults. コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

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

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t,

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

XML 形式以外のフォーマット ファイルbcp と生成された ID 値を使用する方法Using bcp and Generated Identity Values with a Non-XML Format File

既定値と -f スイッチを使用します。Using defaults and -f switch. コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

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

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

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

フォーマット ファイルなしで BULK INSERT を使用して ID 値を維持する方法Using BULK INSERT and Keeping Identity Values without a Format File

KEEPIDENTITY 引数。KEEPIDENTITY argument. 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 dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
    FROM 'D:\BCP\myIdentity.bcp'
    WITH (
        DATAFILETYPE = 'char',  
        FIELDTERMINATOR = ',',  
        KEEPIDENTITY
        );

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

XML 形式以外のフォーマット ファイルBULK INSERT を使用して ID 値を維持する方法Using BULK INSERT and Keeping Identity Values with a Non-XML Format File

KEEPIDENTITY 引数と FORMATFILE 引数。KEEPIDENTITY and the FORMATFILE argument. 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 dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myIdentity.fmt',
        KEEPIDENTITY
        );

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

フォーマット ファイルなしで BULK INSERT と生成される ID 値を使用する方法Using BULK INSERT and Generated Identity Values without a Format File

既定値を使用します。Using defaults. 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 dbo.myIdentity;  -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = ','
      );

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

XML 形式以外のフォーマット ファイルBULK INSERT と生成された ID 値を使用する方法Using BULK INSERT and Generated Identity Values with a Non-XML Format File

既定値と FORMATFILE 引数を使用します。Using defaults and FORMATFILE argument. 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 dbo.myIdentity;  -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myIdentity.fmt'
        );

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

Using OPENROWSET(BULK...) and Keeping Identity Values with a Non-XML Format File Using OPENROWSET(BULK...) and Keeping Identity Values with a Non-XML Format File

KEEPIDENTITY テーブル ヒントと FORMATFILE 引数。KEEPIDENTITY table hint and FORMATFILE argument. 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 dbo.myIdentity;  -- for testing
INSERT INTO dbo.myIdentity
WITH (KEEPIDENTITY) 
(PersonID, FirstName, LastName, BirthDate)
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myIdentity.bcp', 
        FORMATFILE = 'D:\BCP\myIdentity.fmt'  
        ) AS t1;

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

Using OPENROWSET(BULK...) and Generated Identity Values with a Non-XML Format File Using OPENROWSET(BULK...) and Generated Identity Values with a Non-XML Format File

既定値と FORMATFILE 引数を使用します。Using defaults and FORMATFILE argument. 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 dbo.myIdentity;  -- for testing
INSERT INTO dbo.myIdentity
(FirstName, LastName, BirthDate)
    SELECT FirstName, LastName, BirthDate
    FROM OPENROWSET (
        BULK 'D:\BCP\myIdentity.bcp', 
        FORMATFILE = 'D:\BCP\myIdentity.fmt'  
        ) AS t1;

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

関連タスクRelated Tasks

フォーマット ファイルを作成するにはTo use a format file

一括インポートまたは一括エクスポートのデータ形式を使用するにはTo use data formats for bulk import or bulk export

bcp を使用した互換性のためのデータ形式を指定するにはTo specify data formats for compatibility when using bcp

  1. フィールド ターミネータと行ターミネータの指定 (SQL Server)Specify Field and Row Terminators (SQL Server)

  2. bcp を使用したデータ ファイルのプレフィックス長の指定 (SQL Server)Specify Prefix Length in Data Files by Using bcp (SQL Server)

  3. bcp を使用したファイル ストレージ型の指定 (SQL Server)Specify File Storage Type by Using bcp (SQL Server)

参照See Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
テーブル ヒント (Transact-SQL)Table Hints (Transact-SQL)
データのインポートまたはエクスポート用のフォーマット ファイル (SQL Server)Format Files for Importing or Exporting Data (SQL Server)