大量匯入資料時保留識別值 (SQL Server)

發行項
06/25/2013

您可以將包含識別值的資料檔案大量匯入 Microsoft SQL Server 的執行個體中。根據預設，會忽略所匯入資料檔案中的識別欄位值，SQL Server 會自動指定唯一值。唯一值的依據是資料表建立期間所指定的初始值及累加值。

如果資料檔不包含資料表中識別碼資料行的值，請使用格式檔案指定在匯入資料時應略過資料表中的識別碼資料行。 SQL Server 會自動為資料行指定唯一值。

若要在將資料列大量匯入資料表時，不讓 SQL Server 指定識別值，請使用適當的 keep-identity 命令限定詞。當您指定 keep-identity 限定詞時，SQL Server 會使用資料檔案中的識別值。這些限定詞如下：

命令	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 結構描述下的 AdventureWorks 範例資料庫中，建立一個名為 myTestKeepNulls 的資料表。建立這個資料表。在 SQL Server Management Studio 查詢編輯器中，執行：

USE AdventureWorks;
GO
SELECT * INTO HumanResources.myDepartment 
   FROM HumanResources.Department
      WHERE 1=0;
GO
SELECT * FROM HumanResources.myDepartment;

myDepartment 所依據的 Department 資料表已將 IDENTITY_INSERT 設為 OFF。因此，若要將資料匯入至識別資料行，您必須指定 KEEPIDENTITY 或 -E。

範例資料檔

大量匯入範例中所使用的資料檔包含從 HumanResources.Department 資料表中大量匯出的原生格式資料。若要建立資料檔，請在 Microsoft Windows 命令提示字元中，輸入：

bcp AdventureWorks.HumanResources.Department out myDepartment-n.Dat -n -T

範例格式檔案

大量匯入範例使用 XML 格式檔案 myDepartment-f-x-n.Xml，而它是使用原生資料格式。這個範例使用 bcp 從 AdventureWorks 資料庫的 HumanResources.Department 資料表來產生這個格式檔案。在 Windows 命令提示字元中，輸入：

bcp AdventureWorks.HumanResources.Department format nul -n -x -f myDepartment-f-n-x.Xml -T

如需有關建立格式檔案的詳細資訊，請參閱＜建立格式檔案 (SQL Server)＞。

A.使用 bcp 並保留識別值

下列範例示範如何在使用 bcp 大量匯入資料時保留識別值。 bcp 命令會使用格式檔案 myDepartment-f-n-x.Xml，並包含下列參數：

限定詞	說明
-E	指定將資料檔案中的識別值用於識別欄位。
-T	指定以信任連接將 bcp 公用程式連接到 SQL Server。

在 Windows 命令提示字元中，輸入：

bcp AdventureWorks.HumanResources.myDepartment in C:\myDepartment-n.Dat -f C:\myDepartment-f-n-x.Xml -E -T

B.使用 BULK INSERT 並保留識別值

下列範例使用 BULK INSERT 將資料從 myDepartment-c.Dat 檔案大量匯入 AdventureWorks.HumanResources.myDepartment 資料表。該陳述式使用 myDepartment-f-n-x.Xml 格式檔案，並包含 KEEPIDENTITY 選項，以確保資料檔案中的任何識別值都會保留下來。

在 SQL Server Management Studio 查詢編輯器中，執行：

USE AdventureWorks;
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 並保留識別值

下列範例使用 OPENROWSET 大量資料列集提供者將資料從 myDepartment-c.Dat 檔案大量匯入 AdventureWorks.HumanResources.myDepartment 資料表。該陳述式使用 myDepartment-f-n-x.Xml 格式檔案，並包含 KEEPIDENTITY 提示，以確保資料檔中的任何識別值都會保留下來。

在 SQL Server Management Studio 查詢編輯器中，執行：

USE AdventureWorks;
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