Beispiel #1
0
        void BulkInsert(FetchTables ft, bool ParallelExecution, int MaxThreads, bool OracleSpool)
        {
            // If table is not incrementally loaded, all data is loaded to a temp table which is then switched
            // If incremental load, data is loaded into table

            // create target schema if not exists
            TargetDataAccess.ExecSqlNonQuery("IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = '" + ft.TargetSchema + "') BEGIN EXEC('CREATE SCHEMA " + ft.TargetSchema + "') END");

            // 1. Create temp table
            if (!ft.Incremental)
            {
                TargetDataAccess.ExecSqlNonQuery(ft.DropTempTableSql);
                TargetDataAccess.ExecSqlNonQuery(ft.CreateTempTableSql);
            }
            else
            {
                // if incremental, check if target table exists otherwise create it
                if (!CheckIfTableExists(ft))
                {
                    TargetDataAccess.ExecSqlNonQuery(ft.CreateTableSql);
                }
            }

            // 2. Bulk insert data to temp table
            Parallel.ForEach(ft.Shards, new ParallelOptions {
                MaxDegreeOfParallelism = MaxThreads / 10
            }, (shard) =>
                             //foreach(Shard shard in ft.Shards)
            {
                Console.WriteLine($"Bulk inserting {shard.Name} on thread {Thread.CurrentThread.ManagedThreadId}");
                if (BulkInsert(shard, ft.Incremental, OracleSpool))
                {
                    ft.NumShardsInsertedSuccessfully++;
                }
            }
                             );

            if (!ft.Incremental)
            {
                // if all shards where inserted successfully
                if (ft.AllShardsBulkInsertedSuccessfully)
                {
                    // 3. Drop existsing table
                    TargetDataAccess.ExecSqlNonQuery(ft.DropTableSql);
                    // 4. Rename temp table
                    TargetDataAccess.ExecSqlNonQuery(ft.SwitchTableSql);
                }
            }
        }
Beispiel #2
0
        bool BulkInsert(Shard shard, bool Incremental, bool OracleSpool)
        {
            string stepid = Guid.NewGuid().ToString();

            Logger.LogStepStart(stepid, shard.Name, "BULK INSERT " + shard.Name);
            string bulkinsertsql = "bulk insert " + shard.TargetSchema + "." + shard.TableName + (Incremental?" ": "_tmp ") +
                                   "from '" + csvFolder + shard.TableName + "\\" + shard.Name + ".csv' " +
                                   "with( " +
                                   "   format = 'csv', " +
                                   "   fieldterminator='" + csvSeparator + "'," +
                                   "   codepage = '" + (OracleSpool ? "1252" : "65001") + "' " +
                                   (OracleSpool?"":",   firstrow = 2 ") +
                                   (OracleSpool ?"":",   fieldquote = '\"'") +
                                   ")";



            bool b = TargetDataAccess.ExecSqlNonQuery(bulkinsertsql);

            Logger.LogStepEnd(stepid);
            return(b);
        }