private void recurseCopyData(Table sourceTable, Database targetDatabase, CopyPortion portion, List <Table> processedTables, ForeignKey recursionFK) { // Copy n% of the fact table and then traverse each foreign key needed to restore RI Table targetTable = targetDatabase.Tables[sourceTable.Name, sourceTable.Schema]; this.disabledForeignKeys.AddRange(disableTableForeignKeys(targetTable)); if (isLargeTable(sourceTable)) { if (recursionFK == null) { CopyTableSample(sourceTable, targetTable, portion); processedTables.Add(sourceTable); } else { CopyTableMissingKeys(sourceTable, recursionFK, targetDatabase, portion); processedTables.Add(sourceTable); } } else { // It's small so just copy 100% CopyTableTopN(sourceTable, targetTable, new CopyPortionPercentage(100)); processedTables.Add(sourceTable); } //ToDo: Break out of circular / self referencing FKs foreach (ForeignKey sourceForeignKey in sourceTable.ForeignKeys) { if (sourceForeignKey.IsEnabled) { Table referencedTable = sourceDatabase.Tables[sourceForeignKey.ReferencedTable, sourceForeignKey.ReferencedTableSchema]; if (!processedTables.Contains(referencedTable)) { recurseCopyData(referencedTable, targetDatabase, portion, processedTables, sourceForeignKey); } else { this.disabledForeignKeys.AddRange(disableTableForeignKeys(targetTable)); CopyTableMissingKeys(referencedTable, sourceForeignKey, targetDatabase, portion); processedTables.Add(referencedTable); } } } enableTableForeignKeys(this.disabledForeignKeys); }
private long CopyTableTopN(Table sourceTable, Table targetTable, CopyPortion portion) { uint percent = portion.AsPercentage(); long ret = 0; String sql = GetSqlTopNCopy(sourceTable, percent); using (SqlConnection sourceConnection = sourceServer.ConnectionContext.SqlConnectionObject) { sourceConnection.Open(); sourceConnection.ChangeDatabase(sourceTable.Parent.Name); using (SqlCommand sqlCommand = new SqlCommand(sql, sourceConnection)) { ret = this.BulkCopyData(sourceTable, sqlCommand, targetTable); } sourceConnection.Close(); } return(ret); }
public void copyDataForAllTables(CopyPortion portion) { List <Table> processedTables = new List <Table>(); // Get largest ( by rowcount) unprocessed table Table nextLargestTable = sourceDatabase.Tables.Cast <Table>() .Where(t => t.IsSystemObject == false) .Except(processedTables) .OrderBy(t => t.RowCount) .Reverse() .FirstOrDefault(); while (nextLargestTable != null) { recurseCopyData(nextLargestTable, targetDatabase, portion, processedTables, null); nextLargestTable = sourceDatabase.Tables.Cast <Table>() .Where(t => t.IsSystemObject == false) .Except(processedTables) .OrderBy(t => t.RowCount) .Reverse() .FirstOrDefault(); } }
private long CopyTableMissingKeys(Table sourceTable, ForeignKey sourceForeignKey, Database targetDatabase, CopyPortion portion) { long ret; String sql; Table targetTable = targetDatabase.Tables[sourceForeignKey.ReferencedTable, sourceForeignKey.ReferencedTableSchema]; if (sourceTable.RowCount > 0) { sql = GetSqlCopyDataMissingKeys(sourceForeignKey); } else { uint percent = portion.AsPercentage(); sql = GetSqlSampleCopy(sourceTable, percent); } using (SqlConnection sourceConnection = sourceServer.ConnectionContext.SqlConnectionObject) { sourceConnection.Open(); sourceConnection.ChangeDatabase(targetDatabase.Name); using (SqlCommand sqlMissingKeysCommand = new SqlCommand(sql, sourceConnection)) { ret = this.BulkCopyData(sourceTable, sqlMissingKeysCommand, targetTable); } sourceConnection.Close(); return(ret); } }