private void InsertOrUpdateData(TableData table, SqlConnection conn, SqlTransaction tran, string sourceTable = null) { var insert = false; if (Update) { var clear = SqlClearOptions.HasFlag(table.Schema.IsDimension() ? SqlClearOptions.Dimensions : SqlClearOptions.Facts); if (!clear && _cutoff.HasValue) { var pfield = SqlUpdateUtil.GetPartitionField(table.Schema); if (pfield != null) { var sql = string.Format("DELETE {0} FROM {0} {1}", Escape(table.Name), SqlUpdateUtil.GetUpdateCriteria(table.Schema, pfield, true, _cutoff)); new SqlCommand(sql, conn, tran) { CommandTimeout = Timeout }.ExecuteNonQuery(); insert = true; } } else { new SqlCommand(string.Format("DELETE FROM {0}", Escape(table.Name)), conn, tran).ExecuteNonQuery(); insert = true; } } SqlCommand cmd; //Merge new dimension values if (Update && !insert) { var sql = new StringBuilder(); sql.AppendFormat(@"MERGE [{0}] WITH (TABLOCK) AS Target USING {1} AS Source ON {2} ", table.Name, sourceTable ?? "@Data", //Join criteria string.Join(" AND ", (table.Schema.Keys.Length > 0 ? table.Schema.Keys : table.Schema.Dimensions).Select( field => string.Format("{0} = {1}", Escape("Target", field.Value.Name), Escape("Source", field.Value.Name))))); if (table.Schema.Fields.Any(f => !table.Schema.IsKey(f))) { sql.AppendFormat(@"WHEN Matched THEN UPDATE SET {0}", //Update fields string.Join(", ", table.Schema.Fields.Where(f => !table.Schema.IsKey(f)).Select(field => string.Format( field.FieldType == FieldType.Fact ? "{0} = [Target].{0} + {1}" : "{0} = {1}", // <- Consider this. What if dimensions have measures? Escape(field.Name), Escape("Source", field.Name))))); } sql.AppendFormat("WHEN NOT MATCHED THEN INSERT ({0}) VALUES ({1});", //Insert fields string.Join(", ", table.Schema.Fields.Select(field => Escape(field.Name))), string.Join(", ", table.Schema.Fields.Select(field => Escape("Source", field.Name))) ); cmd = new SqlCommand(sql.ToString(), conn, tran); } else { cmd = new SqlCommand( string.Format("INSERT {0} WITH (TABLOCK) SELECT * FROM {1}", Escape(table.Name), sourceTable ?? "@Data"), conn, tran); } cmd.CommandTimeout = Timeout; if (sourceTable == null) { var p = cmd.Parameters.AddWithValue("@Data", new SqlRecordAdapter(table)); p.SqlDbType = SqlDbType.Structured; p.TypeName = GetTableTypeName(table); } try { cmd.ExecuteNonQuery(); } catch (ArgumentException ex) { //Ignore. SqlCommand throws ArgumentException when table is empty. } }
public void Process(string tempDirectory, IEnumerable <TableData> sourceTables, IJobSpecification job) { var tables = sourceTables.Select(t => t.Schema).ToArray(); using (var server = new Server()) { server.Connect(ConnectionString); var db = server.Databases.Find(DbName); if (!Update) { if (db != null) { db.Drop(); } CreateSchema(db, server, tables); db = server.Databases.Find(DbName); } //server.CaptureXml = true; <- Doesn't work with QueryBinding and errors from empty partitions marked as never processed. foreach (var table in sourceTables) { var processingType = !Update && IncrementalUpdate.HasFlag( table.Schema.IsDimension() ? SqlClearOptions.Dimensions : SqlClearOptions.Facts) ? ProcessType.ProcessAdd : ProcessType.ProcessFull; if (table.Schema.IsDimension()) { ProcessPartition(db, table.Name, table.Name, processingType); } else { var partition = SqlUpdateUtil.GetPartitionField(table.Schema); if (partition != null) { ProcessPartition(db, table.Name, GetTransientPartitionName(table.Schema), ProcessType.ProcessFull, string.Format("SELECT [{0}].* FROM [{0}] {1}", table.Name, SqlUpdateUtil.GetUpdateCriteria(table.Schema, partition, true, date: ReferenceDate))); ProcessPartition(db, table.Name, table.Name, processingType, string.Format("SELECT [{0}].* FROM [{0}] {1}", table.Name, SqlUpdateUtil.GetUpdateCriteria(table.Schema, partition, false, date: ReferenceDate, cutoff: CutOff))); } else { ProcessPartition(db, table.Name, table.Name, processingType); } } } //server.ExecuteCaptureLog(true, true); } }