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