大量匯入資料時保留識別值 (SQL Server)
您可以將包含識別值的資料檔案大量匯入 Microsoft SQL Server 的執行個體中。 根據預設,會忽略所匯入資料檔案中的識別資料行值,SQL Server 會自動指定唯一值。 唯一值的依據是資料表建立期間所指定的初始值及累加值。
如果資料檔不包含資料表中識別碼資料行的值,請使用格式檔案指定在匯入資料時應略過資料表中的識別碼資料行。 SQL Server會自動指派資料行的唯一值。
若要在將資料列大量匯入資料表時,不讓 SQL Server 指派識別值,請使用適當的 keep-identity 命令限定詞。 當您指定 keep-identity 限定詞時,SQL Server 會使用資料檔案中的識別值。 這些限定詞如下:
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)。
注意
若要建立可用於多個資料表中或可在不參考任何資料表的情況下從應用程式進行呼叫的自動遞增數字,請參閱 序號。
範例
本主題中的範例會使用 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
從 HumanResources.Department
資料庫的 AdventureWorks
資料表來產生這個格式檔案。 請在 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
相關工作
若要使用格式檔案
若要使用大量匯入或大量匯出的資料格式
若要在使用 bcp 時指定相容性的資料格式
另請參閱
BACKUP (Transact-SQL)
bcp 公用程式
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
資料表提示 (Transact-SQL)