データの一括インポート時の ID 値の保持
ID 値を含んでいるデータ ファイルを Microsoft SQL Server のインスタンスに一括インポートできます。既定では、インポートされたデータ ファイルの ID 列の値は無視され、SQL Server によって固有の値が自動的に割り当てられます。固有の値は、テーブル作成時に指定されたシード値と増分値に基づいています。
データ ファイルにテーブルの ID 列の値が含まれていない場合は、フォーマット ファイルを使用してデータをインポートするときにテーブルの ID 列をスキップするように指定します。SQL Server は固有の値をこの列に自動的に割り当てます。
テーブルにデータ行を一括インポートするときに SQL Server が ID 値を割り当てないようにするには、適切な keep-identity コマンド修飾子を使用します。keep-identity 修飾子を指定すると、SQL Server ではデータ ファイルの ID 値を使用します。このような修飾子は次のとおりです。
コマンド |
Keep-identity 修飾子 |
修飾子の種類 |
|---|---|---|
bcp |
-E |
スイッチ |
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)」を参照してください。
例
このトピックの例では、INSERT ... SELECT * FROM OPENROWSET(BULK...) を使用し、既定値を保持して、データを一括インポートします。
サンプル テーブル
この一括インポートの例では、dbo スキーマに基づいて、myTestKeepNulls という名前のテーブルを AdventureWorks2008R2 サンプル データベースに作成する必要があります。このテーブルを作成するには、SQL Server Management Studio クエリ エディターで次のコードを実行します。
USE AdventureWorks2008R2;
GO
SELECT * INTO HumanResources.myDepartment
FROM HumanResources.Department
WHERE 1=0;
GO
SELECT * FROM HumanResources.myDepartment;
USE AdventureWorks2008R2;
GO
SELECT * INTO HumanResources.myDepartment
FROM HumanResources.Department
WHERE 1=0;
GO
SELECT * FROM HumanResources.myDepartment;
myDepartment の基になる Department テーブルには IDENTITY_INSERT があり、OFF に設定されています。したがって、ID 列にデータをインポートするには、KEEPIDENTITY または -E を指定する必要があります。
サンプル データ ファイル
一括インポートの例で使用したデータ ファイルには、ネイティブ形式で HumanResources.Department テーブルから一括エクスポートされたデータが含まれています。このデータ ファイルを作成するには、Microsoft Windows のコマンド プロンプトで次のように入力します。
bcp AdventureWorks2008R2.HumanResources.Department out myDepartment-n.Dat -n -T
bcp AdventureWorks2008R2.HumanResources.Department out myDepartment-n.Dat -n -T
サンプル フォーマット ファイル
この一括インポートの例では myDepartment-f-x-n.Xml という XML フォーマット ファイルを使用します。ここではネイティブのデータ形式が使用されています。この例では、bcp を使用して、AdventureWorks2008R2 データベースの HumanResources.Department テーブルからこのフォーマット ファイルを作成します。Windows のコマンド プロンプトで、次のように入力します。
bcp AdventureWorks2008R2.HumanResources.Department format nul -n -x -f myDepartment-f-n-x.Xml -T
bcp AdventureWorks2008R2.HumanResources.Department format nul -n -x -f myDepartment-f-n-x.Xml -T
フォーマット ファイルの作成方法の詳細については、「フォーマット ファイルの作成」を参照してください。
A. bcp を使用して ID 値を保持する方法
次の例では、bcp を使用してデータを一括インポートするときに ID 値を保持する方法について説明します。bcp コマンドは、フォーマット ファイル myDepartment-f-n-x.Xml を使用し、次のスイッチを含んでいます。
修飾子 |
説明 |
|---|---|
-E |
データ ファイルの ID 値を ID 列に使用するように指定します。 |
-T |
信頼関係接続で、bcp ユーティリティを SQL Server に接続するように指定します。 |
Windows のコマンド プロンプトで、次のように入力します。
bcp AdventureWorks2008R2.HumanResources.myDepartment in C:\myDepartment-n.Dat -f C:\myDepartment-f-n-x.Xml -E -T
bcp AdventureWorks2008R2.HumanResources.myDepartment in C:\myDepartment-n.Dat -f C:\myDepartment-f-n-x.Xml -E -T
B. BULK INSERT を使用して ID 値を保持する方法
次の例では、BULK INSERT を使用して、myDepartment-c.Dat ファイルから AdventureWorks2008R2.HumanResources.myDepartment テーブルにデータを一括インポートします。このステートメントは、myDepartment-f-n-x.Xml フォーマット ファイルを使用し、データ ファイル内の任意の ID 値が必ず保持されるように KEEPIDENTITY オプションを含んでいます。
SQL Server Management Studio クエリ エディターで、次のように実行します。
USE AdventureWorks2008R2;
GO
DELETE HumanResources.myDepartment;
GO
BULK INSERT HumanResources.myDepartment
FROM 'C:\myDepartment-n.Dat'
WITH (
KEEPIDENTITY,
FORMATFILE='C:\myDepartment-f-n-x.Xml'
);
GO
SELECT * FROM HumanResources.myDepartment;
USE AdventureWorks2008R2;
GO
DELETE HumanResources.myDepartment;
GO
BULK INSERT HumanResources.myDepartment
FROM 'C:\myDepartment-n.Dat'
WITH (
KEEPIDENTITY,
FORMATFILE='C:\myDepartment-f-n-x.Xml'
);
GO
SELECT * FROM HumanResources.myDepartment;
C. OPENROWSET を使用して ID 値を保持する方法
次の例では、OPENROWSET 一括行セット プロバイダーを使用して、myDepartment-c.Dat ファイルから AdventureWorks2008R2.HumanResources.myDepartment テーブルにデータを一括インポートします。このステートメントは、myDepartment-f-n-x.Xml フォーマット ファイルを使用し、データ ファイル内の任意の ID 値が必ず保持されるように KEEPIDENTITY ヒントを含んでいます。
SQL Server Management Studio クエリ エディターで、次のように実行します。
USE AdventureWorks2008R2;
GO
DELETE HumanResources.myDepartment;
GO
INSERT INTO HumanResources.myDepartment
with (KEEPIDENTITY)
(DepartmentID, Name, GroupName, ModifiedDate)
SELECT *
FROM OPENROWSET(BULK 'C:\myDepartment-n.Dat',
FORMATFILE='C:\myDepartment-f-n-x.Xml') as t1;
GO
USE AdventureWorks2008R2;
GO
DELETE HumanResources.myDepartment;
GO
INSERT INTO HumanResources.myDepartment
with (KEEPIDENTITY)
(DepartmentID, Name, GroupName, ModifiedDate)
SELECT *
FROM OPENROWSET(BULK 'C:\myDepartment-n.Dat',
FORMATFILE='C:\myDepartment-f-n-x.Xml') as t1;
GO