不同版本的SQL Server之間資料匯出匯入的方法及效能比較

工作中有段時間常常涉及到不同版本的資料庫間匯出匯入資料的問題，索性整理一下，並簡單比較下效能，有所遺漏的方法也歡迎討論、補充。

00.建立測試環境

01.使用SQL Server Import and Export Tool

02.使用Generate Scripts

03.使用BCP

04.使用SqlBulkCopy

05.使用Linked Server進行資料移轉

06.使用RedGate的SQL Data Compare

07.結果對比

可以先看下測試的結果

 

 

 

00.建立測試環境

建立一個測試的環境，一個資料來源資料庫，版本為SQL Server 2008，一個目標資料庫，版本為SQL Server 2000。

實驗環境如所示，來源資料庫使用語句產生了100萬的測試資料。

 

建立測試表並產生100萬的測試資料

  IF OBJECT_ID('DEMOTABLE') IS NOT NULL       DROP TABLE DEMOTABLE  GO  CREATE TABLE DEMOTABLE      (        COL1 VARCHAR(50) ,        COL2 VARCHAR(50) ,        COL3 VARCHAR(50)      )  INSERT  INTO DEMOTABLE         SELECT TOP 1000000                 NEWID() ,                 NEWID() ,                 NEWID()         FROM    MASTER..SPT_VALUES T1                 INNER JOIN MASTER..SPT_VALUES T2 ON 1 = 1                 INNER JOIN MASTER..SPT_VALUES T3 ON 1 = 1

 

 01.使用SQL Server Import and Export Tool

使用SQL Server Import and Export Tool進行資料的匯出，也可以在目標資料庫端使用Import進行匯入，這部分套件也是SSIS的一部分。

在來源資料庫上右鍵，選擇Task -> Export Data

分別填寫來源資料庫和目標資料庫的串連資訊。

 

 

選擇“copy data from one or more tables or views”

選擇需要導資料的表，並且可以編輯列的Mapping關係。

可以選擇立即執行或者儲存為SSIS的包，用於執行計畫等其他用途。

這裡我們選擇立即執行。

注意匯入的時候如果遇到如下的錯誤

Error 0xc02020f4: Data Flow Task: The column "Tel" cannot be processed because more than one code page (936 and 1252) are specified for it.
(SQL Server Import and Export Wizard)

是因為兩邊的資料庫的Collation設定不一樣造成的，需要設定同樣的Collation。

• 用時約1分30秒

02.使用Generate Scripts產生指令碼

在來源資料庫上右鍵，選擇Task -> Geneate Scripts...

配置相關資訊，注意選擇資料庫的版本並將Script Data設定成True。

這裡需要注意，因為有100萬的資料，所以匯出的SQL檔案就有400多M，所以用SQL Server Management Studio是打不開的。

所以只能使用sqlcmd執行。

sqlcmd語句 

C:\>sqlcmd -i export.sql -d ExportDataDemo_Destination -s 192.168.21.165 -U sa -P 1234567890

用時約28分鐘

 

 03.使用BCP進行匯出匯入

在嘗試了前面兩個效率低下的工具之後，我們終於開始嘗試下SQL Server中專門用於導資料的工具：BCP。

關於BCP的詳細用法可以參見MSDN的協助文檔。

我們先使用BCP匯出資料。

-U和-P後面分別為資料庫的使用者名稱和密碼。

我們可以看到100萬的資料匯出僅用了1.8秒。

現在我們再使用BCP進行匯入。

執行後發現，匯入資料使用了20.8秒，還是很快的。

• 用時1.872秒+20.810秒=22.682秒

•  

 04.使用SqlBulkCopy

.NET Framework 2.0中增加的SqlBulkCopy類可以進行高效的資料移轉動作，這也為代碼實現資料移轉提供了介面。

並且SqlBulkCopy類提供了修改欄位Mapping關係的方法ColumnMappings。

 使用SqlBulkCopy類進行資料移轉

  using System;  using System.Data;  using System.Data.SqlClient;    namespace BulkInsert  {      static class Program      {          static void Main()         {             DateTime dateTimeStart = DateTime.Now;             Console.WriteLine("Start Insert:" + dateTimeStart.ToString("HH:mm:ss fff"));             //匯入匯出的資料庫連接             SqlConnection connectionDestination = new SqlConnection("Server =.; User ID=datascan; Password=DTSbsd7188228; Initial CataLog=ExportDataDemo_Destination;");             SqlConnection connectionSource = new SqlConnection("Server =.; User ID=datascan; Password=DTSbsd7188228; Initial CataLog=ExportDataDemo_Source;");              //執行個體化一個SqlBulkCopy             var bulker = new SqlBulkCopy(connectionDestination) { DestinationTableName = "DEMOTABLE", BulkCopyTimeout = 600 };              //擷取來源資料庫的資料             SqlCommand sqlcmd = new SqlCommand("SELECT * FROM DEMOTABLE", connectionSource);             SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlcmd);             DataTable dataTableSource = new DataTable();             sqlDataAdapter.Fill(dataTableSource);              //可以重新定義欄位的Mapping關係             //SqlBulkCopyColumnMapping sqlBulkCopyColumnMapping = new SqlBulkCopyColumnMapping("COL1", "NEW_COL1");             //bulker.ColumnMappings.Add(sqlBulkCopyColumnMapping);             connectionDestination.Open();             bulker.WriteToServer(dataTableSource);             bulker.Close();             DateTime dateTimeEnd = DateTime.Now;             Console.WriteLine("Insert Ending:" + dateTimeEnd.ToString("HH:mm:ss fff"));         }     } }

執行後

• 用時14.8秒

 

05.使用Linked Server進行資料移轉

先在來源資料庫上對目標資料庫建立Linked Server，或者反過來也行。 

建立Linked Server

 EXEC sp_addlinkedserver @server = 'LinkedServerToDemo',     @srvproduct = 'Export Data Testing', @provider = 'MSDASQL',     @provstr = 'DRIVER={SQL Server};SERVER=192.168.21.165;UID=sa;PWD=password;'

是用INSERT INTO...SELECT...進行匯入

  DECLARE @begin_date DATETIME  DECLARE @end_date DATETIME  SELECT  @begin_date = GETDATE()    INSERT  INTO LinkedServerToDemo.ExportDataDemo_Destination.dbo.DEMOTABLE          SELECT  *          FROM    ExportDataDemo_Source.dbo.DEMOTABLE            SELECT  @end_date = GETDATE() SELECT  DATEDIFF(ms, @begin_date, @end_date) AS '用時/毫秒' 

執行用時

• 用時7.97分鐘

 

06.使用RedGate的SQL Data Compare進行資料移轉

第三方的工具，有資料庫結構比較的工具SQL Compare和資料比較工具SQL Data Compare。

執行

因為也是產生INSERT的SQL執行的，所以就不做過多比較了，上面已經測試過了。

 

 

07.結果對比

因為這裡測試的環境有網路和表結構的特殊情況，不能說明所有情況下效能的差異，但是也可作為參考之用。

下面給出比較結果。