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

  • [アーティクル]

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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 値を使う方法
 ● XML 形式以外のフォーマット ファイルで BULK INSERT と生成された 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)」、「Table Hints (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 ユーティリティ を使用し、 myIdentity.fmtのスキーマに基づいて XML 以外のフォーマット ファイル myIdentityを生成します。 bcp コマンドを使用してフォーマット ファイルを作成するには、 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;

Related Tasks

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

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

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)