Bulk Insert Data Into SQL Server From Oracle Using C#

I wrote this post a few years ago on importing data from Oracle into SQL Server. I wanted an alternate way to load data that would perform well, so I looked into a C# solution.
Two things helped a lot. One, there’s a Oracle.ManagedDataAccess package available through NuGet, for Oracle data access. This allows access to Oracle without a client installed, or setting up TNS files.
Second, we would like to leverage the bulk load functionality of SQL Server to load data. .Net provides the SqlBulkCopy object that will let us access that.
In my test runs, I was able to load read 55,000 records from Oracle (3 attributes) and load them into SQL Server in 4 seconds.

using System;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using System.Data.SqlClient;

namespace DbInsert
{
    class Program
    {
        static void Main(string[] args)
        {
            var startTime = DateTime.Now;
            Console.WriteLine("Start");
            string oracleConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={host})(PORT={port}))(CONNECT_DATA=(SERVICE_NAME={service})));User Id={user};Password={pwd}; ";
            string sql = "SELECT Id, Name FROM SourceTable";

            var dt = GetDataTable(oracleConnectionString, sql);
            Console.WriteLine("Record Count: " + dt.Rows.Count.ToString());
            InsertData(dt);
            Console.WriteLine("Stop");
            int timeSpan = (DateTime.Now - startTime).Seconds;
            Console.WriteLine(timeSpan.ToString() + " Seconds");
            Console.ReadLine();
        }

        private static DataTable GetDataTable(
            string connectionString,
            string sql
        )
        {
            var returnDataset = new DataSet();
            using (var connection = new OracleConnection(connectionString))
            {
                connection.Open();
                using (var command = new OracleCommand(sql, connection))
                {
                    command.CommandType = CommandType.Text;

                    var dataAdapter = new OracleDataAdapter(command);
                    dataAdapter.Fill(returnDataset);
                }
            }
            return returnDataset.Tables[0];
        }

        private static void InsertData(DataTable dt)
        {
            string sql = "Truncate table dbo.TargetTable;" + Environment.NewLine;
            string connectionString = "server={server};database={database};integrated security=sspi;";
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(sql, connection))
                {
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                }

                using (var bulkCopy = new SqlBulkCopy(connection))
                {
                    bulkCopy.DestinationTableName = "dbo.TargetTable";
                    bulkCopy.WriteToServer(dt);
                }
            }

        }

    }
}