将数据从SQL Server导出到PostgreSQL

Question

我使用这个例子将数据从SQL Server导出到PostgreSQL，当我开始导出时，由于300,000行需要12分钟，我可以做些什么来加速这个过程，或者你知道另一种方式去做吧？

string SourceDriver = "Driver={SQL Server Native Client 10.0}"; 
OdbcConnection SourceConnection = new OdbcConnection(SourceDriver+ ";Server=10.10.10.10;Database=sourceMSSQL;Uid=sa;Pwd=12345;"); 

string DestDriver = "Driver={PostgreSQL}"; 
OdbcConnection DestConnection = new OdbcConnection(DestDriver+ ";Server=10.10.10.11;Port=5432;Database=destPostgreSQL;Uid=postgres;Pwd=12345;"); 

string SourceSql = "SELECT Code, Label, Model, List, Size, Quantity, City, Family, ExportDate FROM MovPedidosP0"; 
string DestSql = "INSERT INTO tmp_MovPedidosP0_t (Code, Label, Model, List, Size, Quantity, City, Family, ExportDate) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"; 

using(OdbcCommand SourceCommand = new OdbcCommand(SourceSql, SourceConnection)) 
{ 
    SourceConnection.Open(); 
    using(OdbcDataReader SourceReader = SourceCommand.ExecuteReader()) 
    { 
     Console.WriteLine("Exporting..."); 

     DestConnection.Open(); 

     while(SourceReader.Read()) 
     { 
      using(OdbcCommand DestCommand = new OdbcCommand(DestSql, DestConnection)) 
      { 
       DestCommand.Prepare(); 
       DestCommand.Parameters.Clear(); 

       for(int i=0; i<SourceReader.FieldCount; i++) 
       { 
        DestCommand.Parameters.AddWithValue("?ID" + (i+1).ToString(), SourceReader[i]); 
       } 

       DestCommand.ExecuteNonQuery(); 
       TotalRows++; 
      } 
     } 

     DestConnection.Close(); 
    } 
} 

SourceConnection.Close(); 

Answer 1

尝试使用本机NpgsqlConnection和SqlConnection而不是Odbc连接。

http://npgsql.projects.pgfoundry.org/

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx

Answer 2

更简单，如果您导出到使用SSIS和导入与COPY命令的文本文件可能更快。