/// <summary> /// Get output part of SCD sql command /// </summary> /// <param name="tempTableName">SCD temporary table name</param> /// <returns>Output part of SCD sql command</returns> private string GetOutputPart(string tempTableName) { string result = TEMPLATE_OUTPUT; ScdColumnList scdListBKs = new ScdColumnList(); ScdColumnList scdListAttributes = new ScdColumnList(); foreach (string scdTableName in _scdList.Keys) { scdListBKs.AddList(_scdList[scdTableName].BkList); scdListAttributes.AddList(_scdList[scdTableName].AttributeList); } result = result.Replace("<tempTableName_scd>", tempTableName); result = result.Replace("<attribute_select>", SCDHelper.GetSqlAttributeListDoubled(scdListAttributes.ScdColumns, "src.", "DELETED.", "", "", 2)); result = result.Replace("<vaild_from_select>", SCDHelper.GetSqlValidFrom(_scdList, false, "src.", 2)); result = result.Replace("<attribute_insert>", SCDHelper.GetSqlAttributeListDoubled(scdListAttributes.ScdColumns, "", "", SCDConfiguration.POSTFIX_COLUMN_QUELLE, SCDConfiguration.POSTFIX_COLUMN_DWH_VOR_UPDATE, 2)); result = result.Replace("<vaild_from_insert>", SCDHelper.GetSqlValidFrom(_scdList, true, "", 2)); result = result.Replace("<bk_output>", SCDHelper.GetSqlBkList(scdListBKs.ScdColumns, "INSERTED.", 2)); result = result.Replace("<bk_into>", SCDHelper.GetSqlBkList(scdListBKs.ScdColumns, _prefixFK, "", 2)); result = result.Replace("<FK_ID>", _prefixFK + "ID"); return(result); }
/// <summary> /// Get sql coomand for creating SCD temporary table /// </summary> /// <param name="tempTableName">SCD temporary table name</param> /// <param name="enableIndexOnSCD">Disable SCD temporary table nonclusterd index?</param> /// <returns>Sql coomand for creating SCD temporary table</returns> private string GetCreateTempTable(string tempTableName, bool enableIndexOnSCD) { string result = TEMPLATE_CREATE_TEMP_TABLE.Replace("<tempTableName_scd>", tempTableName); string indexBk = TEMPLATE_CREATE_CLUSTERD_INDEX_ON_TEMP_TABLE.Replace("<tempTableName_scd>", tempTableName); string indexAttributes = ""; ScdColumnList scdListBKs = new ScdColumnList(); ScdColumnList scdListAttributes = new ScdColumnList(); foreach (string scdTableName in _scdList.Keys) { scdListBKs.AddList(_scdList[scdTableName].BkList); scdListAttributes.AddList(_scdList[scdTableName].AttributeList); } result = result.Replace("<bk>", SCDHelper.GetSqlBkListWithDataType(scdListBKs.ScdColumns, _prefixFK, "", 2)); result = result.Replace("<attributes>", SCDHelper.GetSqlAttributeListWithDataType(scdListAttributes.ScdColumns, SCDConfiguration.POSTFIX_COLUMN_QUELLE, SCDConfiguration.POSTFIX_COLUMN_DWH_VOR_UPDATE, 2)); result = result.Replace("<valid_from>", SCDHelper.GetSqlValidFromWithDataType(_scdList, 2)); result = result.Replace("<FK_ID>", _prefixFK + "ID"); indexBk = indexBk.Replace("<index_columns>", " " + SCDHelper.GetSqlBkList(scdListBKs.ScdColumns, _prefixFK, "", 2).Substring(3)); //Substring(3): Komma an 3. Stelle entfernen if (enableIndexOnSCD) { foreach (SCDColumn scdColumn in scdListAttributes.ScdColumns) { string index = TEMPLATE_CREATE_NONCLUSTERD_INDEX_ON_TEMP_TABLE .Replace("<tempTableName_scd>", tempTableName) //.TrimStart("[".ToCharArray()).TrimEnd("]".ToCharArray())) .Replace("<index_name>", scdColumn.ColumnName); List <SCDColumn> scdColumns = new List <SCDColumn>(); scdColumns.Add(scdColumn); string columns = SCDHelper.GetSqlAttributeListDoubled(scdColumns, "", "", SCDConfiguration.POSTFIX_COLUMN_QUELLE, SCDConfiguration.POSTFIX_COLUMN_DWH_VOR_UPDATE, 2); columns = " " + columns.Substring(3); // Komma an 3. Stelle entfernen index = index.Replace("<index_columns>", columns); indexAttributes += index + Environment.NewLine + Environment.NewLine; } } return(result + Environment.NewLine + Environment.NewLine + indexBk + Environment.NewLine + Environment.NewLine + indexAttributes); }
/// <summary> /// Get insert part of SCD merge command /// </summary> /// <param name="tempTableScd">SCD temporary table</param> /// <returns>Insert part of SCD sql command</returns> private string GetInsertPart(string tempTableScd) { string result = SCDConfiguration.TEMPLATE_INSERT; result = result.Replace("<GranularityMaxValue>", GranularityMaxValue); result = result.Replace("<scd_tablename>", TableName); result = result.Replace("<tempTableName_scd>", tempTableScd); result = result.Replace("<attributes_insert>", SCDHelper.GetSqlAttributeList(AttributeList, "", POSTFIX_COLUMN_QUELLE, 0)); result = result.Replace("<attributes_scd_insert>", SCDHelper.GetSqlAttributeList(AttributeList, "", "", 2)); result = result.Replace("<attributes_scd_mergeinsert>", SCDHelper.GetSqlAttributeList(AttributeList, "", "", 0)); result = result.Replace("<attributes_values>", SCDHelper.GetSqlAttributeList(AttributeList, "MR.", POSTFIX_COLUMN_QUELLE, 2)); result = result.Replace("<valid_from_values>", SCDHelper.GetSqlValidFrom(TableName, ValidFrom, "MR.", "", 0)); result = result.Replace("<attributes_wehre>", SCDHelper.GetSqlAttributeWhere(AttributeList, "MR.")); result = result.Replace("<attributes_output>", SCDHelper.GetSqlAttributeList(AttributeList, "MR.", POSTFIX_COLUMN_QUELLE, 2)); result = result.Replace("<valid_from_output>", SCDHelper.GetSqlValidFrom(TableName, ValidFrom, "MR.", "", 0)); result = result.Replace("<bk_scd>", SCDHelper.GetSqlBkList(BkList, PrefixFK, "", 2)); result = result.Replace("<bk_select_from_merge>", SCDHelper.GetSqlBkList(BkList, PrefixFK, "", 0)); result = result.Replace("<bk_values>", SCDHelper.GetSqlBkList(BkList, PrefixFK, "MR.", 2)); result = result.Replace("<merge_scd_bk_on>", SCDHelper.GetBkOnList(BkList, PrefixFK)); result = result.Replace("<FK_ID>", PrefixFK + "ID"); return(result); }