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

  • 發行項

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

當必須從某個 Microsoft SQL Server 安裝將資訊複製到其他安裝時，Unicode 原生格式很有用。 對非字元的資料使用原生格式可節省時間，消除在資料類型與字元格式之間，不必要的來回轉換。 對所有字元資料使用 Unicode 字元格式，可以防止在使用不同字碼頁的伺服器之間大量傳送資料期間，失去任何擴充字元。 任何大量匯入方法都可以讀取以 Unicode 原生格式表示的資料檔。

建議使用 Unicode 原生格式，在多個 SQL Server 執行個體之間，使用包含擴充字元或 DBCS 字元的資料檔，大量傳送資料。 若是非字元資料，Unicode 原生格式會使用原生 (資料庫) 資料類型。 若是字元資料，如 char、 nchar、 varchar、 nvarchar、 text、 varchar(max)、 nvarchar(max)及 ntext，Unicode 原生格式會使用 Unicode 字元資料格式。

以 SQLVARIANT 儲存在 Unicode 原生格式資料檔的 sql_variant 資料，會以它在原生格式資料檔的相同方式操作，不同處是 char 及 varchar 值會轉換為 nchar 及 nvarchar，這會讓受影響資料行所需的儲存空間量加倍。 原始中繼資料會加以保留，而且在大量匯入資料表資料行時，這些值會轉換回它們的原始 char 及 varchar 資料類型。

本主題內容：
Unicode 原生格式的命令選項
範例測試條件
 ● 範例資料表
 ● 非 XML 格式檔案範例
範例
 ● 使用 bcp 與 Unicode 原生格式匯出資料
 ● 不使用格式檔案而使用 bcp 與原生格式匯入資料
 ● 使用 bcp 與原生格式匯入非 XML 格式檔案的資料
 ● 不使用格式檔案而使用 BULK INSERT 與原生格式
 ● 對非 XML 格式檔案使用 BULK INSERT 與原生格式
 ● 對非 XML 格式檔案使用 OPENROWSET 與原生格式
相關工作

Unicode 原生格式的命令選項

您可以將 Unicode 原生格式資料匯入資料表，方法是使用 bcp、BULK INSERT 或 INSERT ... SELECT * FROM OPENROWSET(BULK...)。對於 bcp 命令或 BULK INSERT 陳述式，您可以在陳述式中指定資料格式。 對於 INSERT...SELECT * FROM OPENROWSET(BULK...) 陳述式，您必須在格式檔案中指定資料格式。

下列命令選項支援 Unicode 原生格式：

Command 選項 描述
bcp -N 導致 bcp 公用程式使用 Unicode 原生格式，這個格式會對所有非字元資料使用原生 (資料庫) 資料類型，對所有字元 (char、 nchar、 varchar、 nvarchar、 text及 ntext) 資料使用 Unicode 字元資料格式。
BULK INSERT DATAFILETYPE ='widenative' 使用 Unicode 原生格式大量匯入資料。
OPENROWSET N/A 必須使用格式檔案

注意

或者，您可以在格式檔案中按照每個欄位指定格式。 如需詳細資訊，請參閱匯入或匯出資料的格式檔案 (SQL Server)。

範例測試條件

本主題中的範例採用下列定義的資料表、資料檔案與格式檔案。

範例資料表

下列指令碼會建立測試資料庫、名為 myWidenative 的資料表，以及在資料表中填入一些初始值。 在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 Transact-SQL：

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myWidenative ( 
	PersonID smallint NOT NULL,
	FirstName nvarchar(25) NOT NULL,
	LastName nvarchar(30) NOT NULL,
	BirthDate date,
	AnnualSalary money
);

-- Populate table
INSERT TestDatabase.dbo.myWidenative
VALUES 
(1, N'ϴAnthony', N'Grosse', '02-23-1980', 65000.00),
(2, N'❤Alica', N'Fatnowna', '11-14-1963', 45000.00),
(3, N'☎Stella', N'Rossenhain', '03-02-1992', 120000.00);

-- Review Data
SELECT * FROM TestDatabase.dbo.myWidenative;

範例非 XML 格式檔案

SQL Server 支援兩種類型的格式檔案：非 XML 格式和 XML 格式。 非 XML 格式是舊版 SQL Server 所支援的原始格式。 如需詳細資訊，請參閱 非 XML 格式檔案 (SQL Server) 。 下列命令將使用 bcp 公用程式 ，根據 myWidenative.fmt的結構描述產生非 XML 格式檔案 myWidenative。 使用 bcp 命令建立格式檔案時，請指定 format 引數並使用 nul 取代資料檔案路徑。 format 選項也需要 -f 選項。 此外，以此範例為例，限定詞 c 會用於指定字元資料， T 會用於指定使用整合式安全性的信任連線。 請在命令提示字元之下，輸入下列命令：

bcp TestDatabase.dbo.myWidenative format nul -f D:\BCP\myWidenative.fmt -T -N

REM Review file
Notepad D:\BCP\myWidenative.fmt

重要

請確認您的非 XML 格式檔案以歸位字元\換行字元結尾。 否則您可能會收到下列錯誤訊息︰

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

範例

下列範例使用以上所建立的資料庫、資料檔案與格式檔案。

使用 bcp 與 Unicode 原生格式匯出資料

-N 參數與 OUT 命令。 注意︰後續所有範例皆會使用此範例中所建立的資料檔案。 請在命令提示字元之下，輸入下列命令：

bcp TestDatabase.dbo.myWidenative OUT D:\BCP\myWidenative.bcp -T -N

REM Review results
NOTEPAD D:\BCP\myWidenative.bcp

不使用格式檔案而使用 bcp 與原生格式匯入資料

-N 參數與 IN 命令。 請在命令提示字元之下，輸入下列命令：

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myWidenative;"

REM Import data
bcp TestDatabase.dbo.myWidenative IN D:\BCP\myWidenative.bcp -T -N

REM Review results is SSMS

使用 bcp 與原生格式匯入非 XML 格式檔案的資料

-N 與 -f 參數與 IN 命令。 請在命令提示字元之下，輸入下列命令：

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myWidenative;"

REM Import data
bcp TestDatabase.dbo.myWidenative IN D:\BCP\myWidenative.bcp -f D:\BCP\myWidenative.fmt -T -N

REM Review results is SSMS

不使用格式檔案而使用 BULK INSERT 與原生格式

DATAFILETYPE 引數。 在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 Transact-SQL：

TRUNCATE TABLE TestDatabase.dbo.myWidenative; -- for testing
BULK INSERT TestDatabase.dbo.myWidenative
	FROM 'D:\BCP\myWidenative.bcp'
	WITH (
		DATAFILETYPE = 'widenative'
		);

-- review results
SELECT * FROM TestDatabase.dbo.myWidenative;

對非 XML 格式檔案使用 BULK INSERT 與原生格式

FORMATFILE 引數。 在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 Transact-SQL：

TRUNCATE TABLE TestDatabase.dbo.myWidenative; -- for testing
BULK INSERT TestDatabase.dbo.myWidenative
   FROM 'D:\BCP\myWidenative.bcp'
   WITH (
		FORMATFILE = 'D:\BCP\myWidenative.fmt'
		);

-- review results
SELECT * FROM TestDatabase.dbo.myWidenative;

對非 XML 格式檔案使用 OPENROWSET 與原生格式

FORMATFILE 引數。 在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 Transact-SQL：

TRUNCATE TABLE TestDatabase.dbo.myWidenative;  -- for testing
INSERT INTO TestDatabase.dbo.myWidenative
	SELECT *
	FROM OPENROWSET (
		BULK 'D:\BCP\myWidenative.bcp', 
		FORMATFILE = 'D:\BCP\myWidenative.fmt'  
		) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myWidenative;

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

另請參閱

bcp 公用程式
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
資料類型 (Transact-SQL)