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

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)

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

如果資料檔不包含資料表中識別碼資料行的值，請使用格式檔案指定在匯入資料時應略過資料表中的識別碼資料行。 如需其他資訊，請參閱 使用格式檔案以略過資料表資料行 (SQL Server) 。

外框
保留識別值
範例測試條件
 ● 範例資料表 (部分機器翻譯)
 ● 範例資料檔案 (部分機器翻譯)
 ● 非 XML 格式檔案範例
範例
 ● 不使用格式檔案而使用 bcp 並保留識別值
 ● 過非 XML 格式檔案使用 bcp 並保留識別值
 ● 不使用格式檔案而使用 bcp 與產生的識別值
 ● 透過非 XML 格式檔案使用 bcp 與產生的識別值
 ● 不使用格式檔案而使用 BULK INSERT 並保留識別值
 ● 透過非 XML 格式檔案使用 BULK INSERT 並保留識別值
 ● 不使用格式檔案而使用 BULK INSERT 與產生的識別值
 ● 透過非 XML 格式檔案使用 BULK INSERT 與產生的識別值
 ● 透過非 XML 格式檔案使用 OPENROWSET 並保留識別值
 ● 透過非 XML 格式檔案使用 OPENROWSET 與產生的識別值

保留識別值

若要在將資料列大量匯入資料表時，不讓 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)。

注意

若要建立可用於多個資料表中或可在不參考任何資料表的情況下從應用程式進行呼叫的自動遞增數字，請參閱 序號。

範例測試條件

本主題中的範例以下面定義的資料表、資料檔案和格式檔案為基礎。

範例資料表

下列指令碼會建立測試資料庫和名為 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 格式。 非 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 並保留識別值

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

Using bcp and Keeping Identity Values with a Non-XML Format File

-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 與產生的預設值

使用預設值。 請在命令提示字元之下，輸入下列命令：

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

Using bcp and Generated Identity Values with a Non-XML Format File

使用預設值與 -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 並保留識別值

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;

Using BULK INSERT 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
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 與產生的識別值

使用預設值。 在 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;

Using BULK INSERT 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
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)