public static string GetMergeCommand(this SqlServerDestiny destiny, List <string> keys) { string sql = $" Merge {destiny.Table} as Destiny \n"; sql += $" USING #{destiny.Table} as Origin \n"; for (int i = 0; i < keys.Count; i++) { string key = keys[i]; sql += $" {(i == 0 ? "ON" : "AND")} Destiny.[{key}] = Origin.[{key}] "; sql += "\n"; } var columns = destiny.GetColumns(); var columnsWhithoutKeys = columns.Where(x => !keys.Any(y => y == x)).ToList(); sql += $" WHEN MATCHED THEN \n"; sql += $" UPDATE SET \n"; for (int i = 0; i < columnsWhithoutKeys.Count; i++) { string column = columnsWhithoutKeys[i]; sql += $" [{column}] = Origin.[{column}]"; sql += (i < columnsWhithoutKeys.Count - 1) ? "," : ""; sql += "\n"; } sql += $" WHEN NOT MATCHED THEN \n"; sql += $" INSERT ( { string.Join(", ", columns.Select(x => $"[{x}]"))} ) \n"; sql += $" VALUES ( { string.Join(", ", columns.Select(x => $"Origin.[{x}]")) } ) \n"; sql += ";"; return(sql); }
public void DadoUmDestinoVálidoRetornaAsColunasDaTabela() { using (destiny.Connection) { destiny.Connection.Open(); var keys = destiny.GetColumnKeys(); var result = destiny.GetColumns(); var columns = new List <string>() { "Identidade", "Sigla", "NomeDaRegiao" }; Assert.Equal(result, columns); } }