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

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)

您可以將包含識別值的資料檔案大量匯入 Microsoft SQL Server 的執行個體中。

根據預設，會忽略所匯入資料檔案中的識別資料行值，SQL Server 會自動指定唯一值。 唯一值的依據是資料表建立期間所指定的初始值及累加值。

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

保留識別值

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

這些限定詞如下：

命令	保持身份限定詞	限定詞類型
bcp	-E	開關
BULK INSERT	KEEPIDENTITY	引數
INSERT ... SELECT * FROM OPENROWSET(BULK...)	保持身份	資料表提示

如需詳細資訊，請參閱 bcp 公用程式、BULK INSERT（Transact-SQL）、OPENROWSET BULK （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 而不是資料檔案路徑。
• 格式選項也需要 -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;"

使用 bcp 來保留 Identity 值，並使用非 XML 格式檔案

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

搭配非 XML 格式的檔案使用 bcp 和生成的身份識別值

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

使用 BULK INSERT 來保留識別值和非 XML 格式檔案

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;

使用 BULK INSERT 和生成的身份值對非 XML 格式檔案進行操作

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

使用 OPENROWSET BULK 並保留識別值與非 XML 格式檔案

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;

搭配非 XML 格式檔案使用 OPENROWSET BULK 和生成的識別值

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

相關工作

• 大量匯入時保留 null 或預設值 (SQL Server)

• 為大量匯出或匯入準備資料

若要使用格式檔案

• 使用 bcp 建立格式檔案 (SQL Server)

• 使用格式檔案大量匯入資料 (SQL Server)

• 使用格式檔案將資料表資料行對應至資料檔案欄位 (SQL Server)

• 使用格式檔案略過資料欄位 (SQL Server)

• 使用格式檔案以略過資料表資料行 (SQL Server)

若要使用大量匯入或大量匯出的資料格式

• 從舊版 SQL Server 匯入原生和字元格式數據

• 使用字元格式匯入或匯出資料 (SQL Server)

• 使用原生格式匯入或匯出資料 (SQL Server)

• 使用 unicode 字元格式匯入或匯出資料 （SQL Server）

• 使用 Unicode 原生格式匯入或匯出資料 (SQL Server)

若要在使用 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)