protected virtual void GenerateRootTableCopy() { var insertColumns = RootTable.Columns .Where(c => c.IsCopyable) .Where(c => !ExcludedColumns.Contains(c)); var insertColumnNames = insertColumns .Select(c => $"[{c.Name}]"); var insertColumnValues = insertColumns .Select(c => UpdateParameterNames.ContainsKey(c) ? UpdateParameterNames[c] : $"Source.[{c.Name}]"); string insertClause = !insertColumns.Any() ? @"INSERT DEFAULT VALUES" : $@"INSERT ( {string.Join(Separators.Cnlw8, insertColumnNames)}) VALUES ( {string.Join(Separators.Cnlw8, insertColumnValues)})"; string setStatement = PrimaryKeyOutputParameterName != null ? $@" SET {PrimaryKeyOutputParameterName} = SCOPE_IDENTITY();" : ""; ProcedureBody.AppendLine($@" MERGE INTO [{RootTable.Schema.Name}].[{RootTable.Name}] AS Target USING ( SELECT * FROM [{RootTable.Schema.Name}].[{RootTable.Name}] WHERE [{RootTable.PrimaryKey.Column.Name}] = {PrimaryKeyParameterName} ) AS Source ON 1 = 0 WHEN NOT MATCHED BY TARGET THEN {insertClause}{GenerateOutputClause(RootTable)}{setStatement}"); }
protected virtual void GenerateDependentTableCopy(ReferenceGraph.Vertex vertex) { var table = vertex.Table; var dependentReferences = vertex.DependentReferences // Check constraints can lead to nullable dependent reference columns. However, rows needs to be dependent // on something copied, so if there's only one dependency we might as well inner join on it regardless. .Select(r => new { r.ParentColumn, r.ReferencedTable, UseLeftJoin = r.ParentColumn.IsNullable && vertex.DependentReferences.Count > 1 }) .ToReadOnlyList(); var selectColumnNames = dependentReferences .Select((r, i) => $"j{i}.InsertedID j{i}InsertedID"); // If necessary (it usually isn't), make sure something in the row is dependent on something copied. Faster to // or together where-ins than it is to coalesce on all left joined columns and perform a not null check. string fromClause = dependentReferences.All(r => r.UseLeftJoin) ? $@"FROM ( SELECT * FROM [{table.Schema}].[{table.Name}] WHERE {string.Join($"{Separators.Nlw16}OR ", dependentReferences.Select(r => $"[{r.ParentColumn.Name}] IN (SELECT ExistingID FROM {TableVariableNames[r.ReferencedTable]})"))} ) AS copy" : $@"FROM [{table.Schema.Name}].[{table.Name}] copy"; var joinClauses = dependentReferences .Select((r, i) => $"{(r.UseLeftJoin ? "LEFT " : "")}JOIN {TableVariableNames[r.ReferencedTable]} j{i}{Separators.Nlw12}ON copy.[{r.ParentColumn.Name}] = j{i}.ExistingID"); var dependentInsertColumnNames = dependentReferences .Select(r => $"[{r.ParentColumn.Name}]"); var dependentInsertColumnValues = dependentReferences // A potential left join should leave InsertedID null only if the original value was null, since this is a root copy. .Select((r, i) => $"j{i}InsertedID"); var nonDependentInsertColumns = table.Columns .Where(c => c.IsCopyable) .Where(c => !ExcludedColumns.Contains(c)) .Where(c => !dependentReferences.Select(r => r.ParentColumn).Contains(c)); var nonDependentInsertColumnNames = nonDependentInsertColumns .Select(c => $"[{c.Name}]"); var nonDependentInsertColumnValues = nonDependentInsertColumns .Select(c => UpdateParameterNames.ContainsKey(c) ? UpdateParameterNames[c] : $"Source.[{c.Name}]"); ProcedureBody.AppendLine($@" MERGE INTO [{table.Schema.Name}].[{table.Name}] AS Target USING ( SELECT copy.*, {string.Join(Separators.Cnlw12, selectColumnNames)} {fromClause} {string.Join(Separators.Nlw8, joinClauses)} ) AS Source ON 1 = 0 WHEN NOT MATCHED BY TARGET THEN INSERT ( {string.Join(Separators.Cnlw8, dependentInsertColumnNames.Concat(nonDependentInsertColumnNames))}) VALUES ( {string.Join(Separators.Cnlw8, dependentInsertColumnValues.Concat(nonDependentInsertColumnValues))}){GenerateOutputClause(table)}"); }
protected override void GenerateDependentTableCopy(ReferenceGraph.Vertex vertex) { var table = vertex.Table; var dependentReferences = vertex.DependentReferences; // Rows needs to be dependent on something copied, so if there's only one dependency we might as well inner join on it. bool useLeftJoin = dependentReferences.Count > 1; var selectColumnNames = dependentReferences .Select((r, i) => $"j{i}.InsertedID j{i}InsertedID"); // If necessary (it often is), make sure something in the row is dependent on something copied. Faster to // or together where-ins than it is to coalesce on all left joined columns and perform a not null check. string fromClause = useLeftJoin ? $@"FROM ( SELECT * FROM [{table.Schema}].[{table.Name}] WHERE {string.Join($"{Separators.Nlw16} OR ", dependentReferences.Select(r => $"[{r.ParentColumn.Name}] IN (SELECT ExistingID FROM {TableVariableNames[r.ReferencedTable]})"))} ) AS copy" : $@"FROM [{table.Schema.Name}].[{table.Name}] copy"; var joinClauses = dependentReferences .Select((r, i) => new { r.ParentColumn, r.ReferencedTable, JoinString = $"{(useLeftJoin ? "LEFT " : "")}JOIN" }) .Select((r, i) => $"{r.JoinString} {TableVariableNames[r.ReferencedTable]} j{i}{Separators.Nlw12}ON copy.[{r.ParentColumn.Name}] = j{i}.ExistingID"); var dependentInsertColumnNames = dependentReferences .Select(r => $"[{r.ParentColumn.Name}]"); var dependentInsertColumnValues = dependentReferences .Select((r, i) => useLeftJoin ? $"COALESCE(j{i}InsertedID, [{r.ParentColumn.Name}])" : $"j{i}InsertedID"); var nonDependentInsertColumns = table.Columns .Where(c => c.IsCopyable) .Where(c => !ExcludedColumns.Contains(c)) .Where(c => !dependentReferences.Select(r => r.ParentColumn).Contains(c)); var nonDependentInsertColumnNames = nonDependentInsertColumns .Select(c => $"[{c.Name}]"); var nonDependentInsertColumnValues = nonDependentInsertColumns .Select(c => UpdateParameterNames.ContainsKey(c) ? UpdateParameterNames[c] : $"Source.[{c.Name}]"); ProcedureBody.AppendLine($@" MERGE INTO [{table.Schema.Name}].[{table.Name}] AS Target USING ( SELECT copy.*, {string.Join(Separators.Cnlw12, selectColumnNames)} {fromClause} {string.Join(Separators.Nlw8, joinClauses)} ) AS Source ON 1 = 0 WHEN NOT MATCHED BY TARGET THEN INSERT ( {string.Join(Separators.Cnlw8, dependentInsertColumnNames.Concat(nonDependentInsertColumnNames))}) VALUES ( {string.Join(Separators.Cnlw8, dependentInsertColumnValues.Concat(nonDependentInsertColumnValues))}){GenerateOutputClause(table)}"); }