/// <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> /// Creates sql command for creating the SCD table /// </summary> /// <returns>sql command for creating the SCD </returns> public string GetCreateScdTables() { string result = ""; foreach (string tableName in _scdList.Keys) { SCDConfiguration scd = _scdList[tableName]; string bks = SCDHelper.GetSqlBkListWithDataType(scd.BkList, _prefixFK, "", 2); string attributes = SCDHelper.GetSqlColumnListWithDataType(scd.AttributeList, "", "", 2, false, "NULL", ""); string create = TEMPLATE_CREATE_SCD_TABLE.Replace("<tableName>", tableName); create = create.Replace("<GranularityMaxValue>", scd.GranularityMaxValue); create = create.Replace("<bks>", bks); create = create.Replace("<attributes>", attributes); create = create.Replace("<FK_ID>", _prefixFK + "ID"); create += Environment.NewLine + Environment.NewLine; result += create; } return(result); }