データの一括インポート時の ID 値の保持 (SQL Server)

適用対象:yesSQL Server Analytics Platform System (PDW) YesYesAzure SQL Database Azure SQL Managed Instance yesyesAzure Synapse Analytics (すべてのサポートされているバージョン)

ID 値を含んでいるデータ ファイルを Microsoft SQL Server のインスタンスに一括インポートできます。 既定では、インポートされるデータ ファイル内の ID 列の値は無視され、一意SQL Server割り当ては行われます。 固有の値は、テーブル作成時に指定されたシード値と増分値に基づいています。

データ ファイルにテーブルの ID 列の値が含まれていない場合は、フォーマット ファイルを使用してデータをインポートするときにテーブルの ID 列をスキップするように指定します。 詳細については、「 フォーマット ファイルを使用したテーブル列のスキップ (SQL Server) 」を参照してください。

[外枠]
ID 値を維持する
テスト条件の例
 • サンプル テーブル
 • サンプル データ ファイル
 XML 以外のフォーマット ファイルのサンプル
使用例
 • フォーマット ファイルを使用せずに bcp を使用し、ID 値を保持する
 XML 以外のフォーマット ファイルで bcp を使用し、ID 値を保持する
 • フォーマット ファイルを使用せずに bcp と生成された ID 値を使用する
 XML 以外のフォーマット ファイルでの bcp および生成された ID 値の使用
 • フォーマット ファイルBULK INSERT使用して ID 値を保持する
 • XML BULK INSERTファイルを使用して ID 値を保持する方法
 • フォーマット ファイルBULK INSERT ID 値と生成された ID 値の使用
 • XML BULK INSERTファイルで生成された ID 値と生成された ID 値の使用
 XML 以外のフォーマット ファイルで OPENROWSET を使用し、ID 値を保持する
 XML 以外のフォーマット ファイルでの OPENROWSET と生成された ID 値の使用

ID 値を維持する

データ行SQL Server一括インポート中に ID 値を割り当てない場合は、適切な keep-identity コマンド修飾子を使用します。 keep-identity 修飾子を指定すると、SQL Serverの ID 値が使用されます。 このような修飾子は次のとおりです。

command Keep-identity 修飾子 修飾子の種類
bcp -E Switch
BULK INSERT KEEPIDENTITY 引数
INSERT ...SELECT * FROM OPENROWSET(BULK...) KEEPIDENTITY テーブル ヒント

詳細については、「bcp ユーティリティBULK INSERT (Transact-SQL)OPENROWSET (Transact-SQL)、INSERT (Transact-SQL)SELECT (Transact-SQL)、およびテーブル ヒント (Transact-SQL)」を参照してください。

Note

複数のテーブルで使用できる自動的に増分する番号、またはテーブルを参照せずにアプリケーションから呼び出すことができる自動的に増分する番号を作成するには、「シーケンス番号」を参照してください。

テスト条件の例

このトピックの例は、以下に定義されたテーブル、データ ファイル、およびフォーマット ファイルに基づいています。

サンプル テーブル

以下のスクリプトでは、テスト データベースと myIdentityという名前のテーブルが作成されます。 Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。

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

サンプル データ ファイル

メモ帳を使用して、空のファイル D:\BCP\myIdentity.bcp を作成し、次のデータを挿入します。

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 スクリプトを実行して、データ ファイルを作成および設定することもできます。

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 以外のフォーマット ファイルのサンプル

SQL Server は、非 XML 形式と XML 形式の 2 種類のフォーマット ファイルをサポートしています。 XML 以外のフォーマットとは、以前のバージョンの SQL Server でサポートされる従来のフォーマットです。 詳細については、「 XML 以外のフォーマット ファイル (SQL Server) 」を参照してください。 次のコマンドは 、bcp ユーティリティを使用して、 のスキーマに基づいて xml 以外のフォーマット ファイル を生成します myIdentitybcp コマンドを使用してフォーマット ファイルを作成するには、 format 引数を指定し、データ ファイルのパスの代わりに nul を使用します。 format オプションには、次に示す -f オプションが必要です。 さらに、この例では、修飾子 c を使用して文字データを指定し、 t を使用して フィールド ターミネータとしてコンマを指定し、 T を使用して統合セキュリティによる信頼された接続を指定します。 コマンド プロンプトで、次のコマンドを入力します。

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

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

重要

XML 以外のフォーマット ファイルは、キャリッジ リターン\ライン フィードで終わるようにします。 そうしないと、次のエラー メッセージが発生する可能性があります。

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

使用例

次の例では、データベース、データ ファイル、および上記で作成したフォーマット ファイルを使用します。

フォーマット ファイルなしで bcp を使用して ID 値を維持する方法

-E スイッチ。 コマンド プロンプトで、次のコマンドを入力します。

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 値を維持する方法

-E スイッチと -f スイッチ。 コマンド プロンプトで、次のコマンドを入力します。

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 値を使用する方法

既定値を使用します。 コマンド プロンプトで、次のコマンドを入力します。

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 値を使用する方法

既定値と -f スイッチを使用します。 コマンド プロンプトで、次のコマンドを入力します。

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 値を維持する方法

KEEPIDENTITY 引数。 Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。

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 値を維持する方法

KEEPIDENTITY 引数と FORMATFILE 引数。 Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。

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 値を使用する方法

既定値を使用します。 Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。

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 値を使用する方法

既定値と FORMATFILE 引数を使用します。 Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。

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

KEEPIDENTITY テーブル ヒントと FORMATFILE 引数。 Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。

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

既定値と FORMATFILE 引数を使用します。 Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。

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;

関連タスク

フォーマット ファイルを作成するには

一括インポートまたは一括エクスポートのデータ形式を使用するには

bcp を使用した互換性のためのデータ形式を指定するには

  1. フィールド ターミネータと行ターミネータの指定 (SQL Server)

  2. bcp を使用したデータ ファイルのプレフィックス長の指定 (SQL Server)

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

参照

BACKUP (Transact-SQL)
bcp ユーティリティ
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
テーブル ヒント (Transact-SQL)
データのインポートまたはエクスポート用のフォーマット ファイル (SQL Server)