Exporting data from SQL Server to PostgreSQL

Question

I am using this example to export data from SQL Server to PostgreSQL, when I start the export as 300,000 rows takes 12 minutes, what I can do to speed up this process or you know another way to do it?

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();

You might look into PostgreSql's batch processing: stackoverflow.com/questions/758945/… — ChrisLively
– ChrisLively, Commented Mar 21, 2013 at 23:07

Clodoaldo Neto · Accepted Answer · 2013-03-21 23:14:14Z

2

Much simpler and probably faster if you export to a text file using SSIS and import with the COPY command.

answered Mar 21, 2013 at 23:14

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Saunders Over a year ago

Or even use SSIS to copy directly to the PostgresSQL database.

Sergio Flores Over a year ago

I would use SSIS and COPY but I need to export the information through an application and is an end user who will use it.

Vedran · Accepted Answer · 2013-03-21 23:04:24Z

0

Try using the native NpgsqlConnection and SqlConnection instead of Odbc connections.

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

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

answered Mar 21, 2013 at 23:04

Vedran

11.2k6 gold badges54 silver badges60 bronze badges

Collectives™ on Stack Overflow

Exporting data from SQL Server to PostgreSQL

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related