public static bool ColumnIsPrimaryKey(ColumnDefinition columnDefinition, TableDefinition definition, bool AllowUnique = false)
{
if ((columnDefinition.Index != null) && (columnDefinition.Index.IndexType != null) && (columnDefinition.Index.IndexType.IndexTypeKind == IndexTypeKind.Clustered))
{
return(true);
}
if ((AllowUnique) && (columnDefinition.Constraints.Count > 0))
{
foreach (var c in columnDefinition.Constraints)
{
if (c is Microsoft.SqlServer.TransactSql.ScriptDom.UniqueConstraintDefinition)
{
return(true);
}
}
}
if ((definition.TableConstraints != null) && (definition.TableConstraints.Count > 0))
{
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
foreach (var c in definition.TableConstraints)
{
if (!(c is UniqueConstraintDefinition))
{
continue;
}
if (((UniqueConstraintDefinition)c).IsPrimaryKey)
{
foreach (var co in ((UniqueConstraintDefinition)c).Columns)
{
string s = Identifiers2ValueLast(co.Column.MultiPartIdentifier.Identifiers);
if (s.ToLowerInvariant() == ident.ToLowerInvariant())
{
return(true);
}
}
}
}
}
return(false);
}
public override string Translate(TableDefinition tableDefinition, object options)
{
string optionAllias = null;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_alias_src", true);
if (r.Length > 0)
{
optionAllias = (r[0] as TextBox).Text;
}
}
string optionAlliasDest = null;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_alias_dest", true);
if (r.Length > 0)
{
optionAlliasDest = (r[0] as TextBox).Text;
}
}
if (!String.IsNullOrEmpty(optionAllias))
{
optionAllias = optionAllias + '.';
}
if (!String.IsNullOrEmpty(optionAlliasDest))
{
optionAlliasDest = optionAlliasDest + '.';
}
StringBuilder result = new StringBuilder();
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
result.Append($"{optionAlliasDest}{ident} = {optionAllias}{ident},{Environment.NewLine}");
}
return(result.ToString());
}
public override string TranslateExt(CreateTableStatement createTableStatement, object options)
{
TableDefinition tableDefinition = createTableStatement.Definition;
string optionAllias0 = null;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_alias_src", true);
if (r.Length > 0)
{
optionAllias0 = (r[0] as TextBox).Text;
}
}
string optionAlliasDest0 = null;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_alias_dest", true);
if (r.Length > 0)
{
optionAlliasDest0 = (r[0] as TextBox).Text;
}
}
bool bOptionInline = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_inline", true);
if (r.Length > 0)
{
bOptionInline = (r[0] as CheckBox).Checked;
}
}
string sColumnSeparator = Environment.NewLine;
if (bOptionInline)
{
sColumnSeparator = null;
}
bool bOptionExplicitNames = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_explicit_pname", true);
if (r.Length > 0)
{
bOptionExplicitNames = (r[0] as CheckBox).Checked;
}
}
bool bOptionValues = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_values", true);
if (r.Length > 0)
{
bOptionValues = (r[0] as CheckBox).Checked;
}
}
bool bOptionDefault = true;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_default", true);
if (r.Length > 0)
{
bOptionDefault = (r[0] as CheckBox).Checked;
}
}
bool bNotAlready = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_notalready", true);
if (r.Length > 0)
{
bNotAlready = (r[0] as CheckBox).Checked;
}
}
string keywordSep = bOptionInline ? " " : Environment.NewLine;
string optionAllias = optionAllias0;
string optionAlliasDest = optionAlliasDest0;
string tableName = TSQLHelper.Identifiers2Value(createTableStatement.SchemaObjectName.Identifiers);
if (!String.IsNullOrEmpty(optionAllias))
{
optionAllias = optionAllias + '.';
}
if (!String.IsNullOrEmpty(optionAlliasDest))
{
optionAlliasDest = optionAlliasDest + '.';
}
string columnIdent = "\t";
if (bOptionInline)
{
columnIdent = null;
}
StringBuilder result = new StringBuilder();
string sep = null;
// insert into
{
result.Append($"insert into {tableName}({sColumnSeparator}");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
if (!bOptionDefault && TSQLHelper.ColumnIsDefault(columnDefinition))
{
continue;
}
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
//if (!bOptionInline)
result.Append($"{columnIdent}{sep}{ident}{sColumnSeparator}");
//else result.Append($"{sep}{ident}{sColumnSeparator}");
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
result.Append($"){Environment.NewLine}");
}
if (bOptionValues)
{
result.Append($"values(");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
if (!bOptionDefault && TSQLHelper.ColumnIsDefault(columnDefinition))
{
continue;
}
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
if (bOptionExplicitNames)
{
result.Append($"{columnIdent}{sep}{optionAllias}{ident} /*{ident}*/{sColumnSeparator}");
}
else
{
result.Append($"{columnIdent}{sep}{optionAllias}{ident}{sColumnSeparator}");
}
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
result.Append($"){Environment.NewLine}");
}
else
// select
{
result.Append($"select{keywordSep}");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
if (!bOptionDefault && TSQLHelper.ColumnIsDefault(columnDefinition))
{
continue;
}
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
if (bOptionExplicitNames)
{
result.Append($"{columnIdent}{sep}{ident} = {optionAllias}{ident}{sColumnSeparator}");
}
else
{
result.Append($"{columnIdent}{sep}{optionAllias}{ident}{sColumnSeparator}");
}
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
result.Append($"{keywordSep}from {optionAllias0}{Environment.NewLine}");
}
if (bNotAlready)
{
result.Append($"where not exists(select 1 from {tableName} t with (nolock) where");
int i = 0;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
i++;
if (!bOptionDefault && TSQLHelper.ColumnIsDefault(columnDefinition))
{
continue;
}
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
if (i > 1)
{
result.Append($" and");
}
result.Append($" t.{ident} = {optionAllias}{ident}");
}
result.Append($")");
}
result.Append($";");
return(result.ToString());
}
public override string Translate(TableDefinition tableDefinition, object options)
{
string optionAllias = null;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_alias", true);
if (r.Length > 0)
{
optionAllias = (r[0] as TextBox).Text;
}
}
if (!String.IsNullOrEmpty(optionAllias))
{
optionAllias = optionAllias + '.';
}
bool bOptionInline = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_inline", true);
if (r.Length > 0)
{
bOptionInline = (r[0] as CheckBox).Checked;
}
}
bool option_forward_comma = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_forward_comma", true);
if (r.Length > 0)
{
option_forward_comma = (r[0] as CheckBox).Checked;
}
}
string sColumnSeparator = Environment.NewLine;
if (bOptionInline)
{
sColumnSeparator = " ";
}
StringBuilder result = new StringBuilder();
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
if (!option_forward_comma)
{
result.Append($"{optionAllias}{ident},{sColumnSeparator}");
}
else
{
result.Append($"{sColumnSeparator}, {optionAllias}{ident}");
}
}
return(result.ToString());
}
public override string TranslateExt(CreateTableStatement createTableStatement, object options)
{
TableDefinition tableDefinition = createTableStatement.Definition;
string optionSource0 = null;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_source", true);
if (r.Length > 0)
{
optionSource0 = (r[0] as TextBox).Text;
}
}
string optionRowAlias0 = null;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_alias_row", true);
if (r.Length > 0)
{
optionRowAlias0 = (r[0] as TextBox).Text;
}
}
bool optionsSafe = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_safe", true);
if (r.Length > 0)
{
optionsSafe = (r[0] as CheckBox).Checked;
}
}
bool option_ccolumns = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_ccolumns", true);
if (r.Length > 0)
{
option_ccolumns = (r[0] as CheckBox).Checked;
}
}
bool option_insert = GetOptionBoolDef("option_insert", options, false);
bool option_identity_insert = GetOptionBoolDef("option_identity_insert", options, false);
bool option_tab_path = GetOptionBoolDef("option_tab_path", options, false);
bool option_output = GetOptionBoolDef("option_output", options, false);
string tableName = TSQLHelper.Identifiers2Value(createTableStatement.SchemaObjectName.Identifiers);
string tableName0 = TSQLHelper.Identifiers2ValueLast(createTableStatement.SchemaObjectName.Identifiers);
StringBuilder result = new StringBuilder();
string sep = null;
if (option_identity_insert)
{
result.Append($"set identity_insert {tableName} on;{Environment.NewLine}{Environment.NewLine}");
}
// insert into
if (option_insert)
{
string sColumnSeparator = Environment.NewLine;
bool bOptionDefault = true;
string columnIdent = "\t";
result.Append($"insert into {tableName}({sColumnSeparator}");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
if (!bOptionDefault && TSQLHelper.ColumnIsDefault(columnDefinition))
{
continue;
}
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
//if (!bOptionInline)
result.Append($"{columnIdent}{sep}{ident}{sColumnSeparator}");
//else result.Append($"{sep}{ident}{sColumnSeparator}");
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
result.Append($"){Environment.NewLine}");
if (option_output)
{
result.Append($"output '{tableName0}' as [Table_{tableName0}], inserted.*{Environment.NewLine}");
}
}
result.Append($"select{Environment.NewLine}");
// rows
{
sep = null;
int iter = -1;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
iter++;
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
string typ = TSQLHelper.Column2TypeStr(columnDefinition);
string attName = ident;
if (option_ccolumns)
{
attName = $"c{iter}";
}
if (optionsSafe)
{
result.Append($"\t{sep}{ident} = case when {optionRowAlias0}.value('@{attName}', 'nvarchar(max)') = \'NULL\' then null else {optionRowAlias0}.value('@{attName}', '{typ}') end{Environment.NewLine}");
}
else
{
result.Append($"\t{sep}{ident} = {optionRowAlias0}.value('@{attName}', '{typ}'){Environment.NewLine}");
}
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
}
string path = "/root[1]/data[1]/row";
if (option_tab_path)
{
path = $"/root[1]/{tableName0}/row";
}
result.Append($"from @{optionSource0}.nodes('{path}') as t({optionRowAlias0}){Environment.NewLine}");
if (option_identity_insert)
{
result.Append($"{Environment.NewLine}set identity_insert {tableName} off;");
}
return(result.ToString());
}
public override string Translate(TableDefinition tableDefinition, object options)
{
string optionAllias0 = null;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_alias_src", true);
if (r.Length > 0)
{
optionAllias0 = (r[0] as TextBox).Text;
}
}
string optionAlliasDest0 = null;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_alias_dest", true);
if (r.Length > 0)
{
optionAlliasDest0 = (r[0] as TextBox).Text;
}
}
bool bOptionInline = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_inline", true);
if (r.Length > 0)
{
bOptionInline = (r[0] as CheckBox).Checked;
}
}
string sColumnSeparator = Environment.NewLine;
if (bOptionInline)
{
sColumnSeparator = " ";
}
bool bOptionExplicitNames = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_explicit_pname", true);
if (r.Length > 0)
{
bOptionExplicitNames = (r[0] as CheckBox).Checked;
}
}
string optionAllias = optionAllias0;
string optionAlliasDest = optionAlliasDest0;
if (!String.IsNullOrEmpty(optionAllias))
{
optionAllias = optionAllias + '.';
}
if (!String.IsNullOrEmpty(optionAlliasDest))
{
optionAlliasDest = optionAlliasDest + '.';
}
string columnIdent = "\t\t";
if (bOptionInline)
{
columnIdent = null;
}
StringBuilder result = new StringBuilder();
string sep = null;
// line1
{
result.Append($"/*same*/ exists({Environment.NewLine}");
result.Append($"\tselect{sColumnSeparator}");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
if (bOptionInline)
{
result.Append($"{sep}{optionAlliasDest}{ident}");
}
else
{
result.Append($"{columnIdent}{sep}{optionAlliasDest}{ident}{sColumnSeparator}");
}
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
}
//line2
{
if (bOptionInline)
{
result.Append($"{Environment.NewLine}");
}
result.Append($"\tintersect{Environment.NewLine}");
result.Append($"\tselect{sColumnSeparator}");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
if (bOptionInline)
{
result.Append($"{sep}{optionAllias}{ident}");
}
else
{
result.Append($"{columnIdent}{sep}{optionAllias}{ident}{sColumnSeparator}");
}
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
result.Append($"{Environment.NewLine}");
}
result.Append($"){Environment.NewLine}");
return(result.ToString());
}
public override string TranslateExt(CreateTableStatement createTableStatement, object options)
{
TableDefinition tableDefinition = createTableStatement.Definition;
string optionAllias0 = null;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_alias_src", true);
if (r.Length > 0)
{
optionAllias0 = (r[0] as TextBox).Text;
}
}
string optionAlliasDest0 = null;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_alias_dest", true);
if (r.Length > 0)
{
optionAlliasDest0 = (r[0] as TextBox).Text;
}
}
bool option_insertonly = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("option_insertonly", true);
if (r.Length > 0)
{
option_insertonly = (r[0] as CheckBox).Checked;
}
}
bool use_star = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("use_star", true);
if (r.Length > 0)
{
use_star = (r[0] as CheckBox).Checked;
}
}
bool use_intesect = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("use_intesect", true);
if (r.Length > 0)
{
use_intesect = (r[0] as CheckBox).Checked;
}
}
bool use_output = false;
if (options is Control)
{
var r = ((Control)options).Controls.Find("use_output", true);
if (r.Length > 0)
{
use_output = (r[0] as CheckBox).Checked;
}
}
string optionAllias = optionAllias0;
string optionAlliasDest = optionAlliasDest0;
if (!String.IsNullOrEmpty(optionAllias))
{
optionAllias = optionAllias + '.';
}
if (!String.IsNullOrEmpty(optionAlliasDest))
{
optionAlliasDest = optionAlliasDest + '.';
}
string tableName = TSQLHelper.Identifiers2Value(createTableStatement.SchemaObjectName.Identifiers);
string firstColumnIdent = null;
StringBuilder result = new StringBuilder();
string sep = null;
if (use_output)
{
result.Append($"declare @mergeOutput table([action] nvarchar(10)");
sep = ", ";
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
if (!TSQLHelper.ColumnIsPrimaryKey(columnDefinition, tableDefinition, true))
{
continue;
}
string ctype = TSQLHelper.Column2TypeStr(columnDefinition);
result.Append($"{sep}{ident} {ctype}");
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
result.Append($");{Environment.NewLine}");
}
// with src as
{
result.Append($";with src as ({Environment.NewLine}");
if (!use_star)
{
result.Append($"\tselect{Environment.NewLine}");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
if (String.IsNullOrEmpty(firstColumnIdent))
{
firstColumnIdent = ident;
}
result.Append($"\t\t{sep}{ident} = {optionAllias}{ident}{Environment.NewLine}");
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
}
else
{
result.Append($"\tselect *{Environment.NewLine}");
}
result.Append($"\tfrom {tableName} {optionAllias0}{Environment.NewLine}");
result.Append($"){Environment.NewLine}");
}
// , targ as
{
result.Append($", targ as ({Environment.NewLine}");
if (!use_star)
{
result.Append($"\tselect{Environment.NewLine}");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
result.Append($"\t\t{sep}{optionAlliasDest}{ident}{Environment.NewLine}");
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
}
else
{
result.Append($"\tselect *{Environment.NewLine}");
}
result.Append($"\tfrom {tableName} {optionAlliasDest0}{Environment.NewLine}");
result.Append($"){Environment.NewLine}");
}
//merge
{
result.Append($"merge targ as {optionAlliasDest0}{Environment.NewLine}");
result.Append($"using src as {optionAllias0}{Environment.NewLine}");
sep = null;
bool onFound = false;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
if (!TSQLHelper.ColumnIsPrimaryKey(columnDefinition, tableDefinition))
{
continue;
}
onFound = true;
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
result.Append($"on (");
}
else
{
result.Append($" and ");
}
result.Append($"{optionAlliasDest}{ident} = {optionAllias}{ident}");
}
if (!onFound)
{
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
if (!TSQLHelper.ColumnIsPrimaryKey(columnDefinition, tableDefinition, true))
{
continue;
}
onFound = true;
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
result.Append($"on (");
}
else
{
result.Append($" and ");
}
result.Append($"{optionAlliasDest}{ident} = {optionAllias}{ident}");
}
}
if (String.IsNullOrEmpty(sep))
{
result.Append($"on ({optionAlliasDest}{firstColumnIdent} = {optionAllias}{firstColumnIdent}){Environment.NewLine}");
}
else
{
result.Append($"){Environment.NewLine}");
}
}
//insert
{
result.Append($"when not matched by target then{Environment.NewLine}");
result.Append($"\tinsert({Environment.NewLine}");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
if (TSQLHelper.ColumnIsIdentity(columnDefinition))
{
continue;
}
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
result.Append($"\t\t{sep}{ident}{Environment.NewLine}");
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
result.Append($"\t){Environment.NewLine}");
result.Append($"\tvalues({Environment.NewLine}");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
if (TSQLHelper.ColumnIsIdentity(columnDefinition))
{
continue;
}
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
result.Append($"\t\t{sep}{optionAllias}{ident}{Environment.NewLine}");
if (string.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
result.Append($"\t){Environment.NewLine}");
}
//update
if (!option_insertonly)
{
if (use_intesect)
{
result.Append($"when matched and not exists({Environment.NewLine}");
result.Append($"\tselect ");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
if (TSQLHelper.ColumnIsIdentity(columnDefinition))
{
continue;
}
if (TSQLHelper.ColumnIsPrimaryKey(columnDefinition, tableDefinition))
{
continue;
}
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
result.Append($"{sep}{optionAlliasDest}{ident}");
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
result.Append($"{Environment.NewLine}");
result.Append($"\tintersect{Environment.NewLine}");
result.Append($"\tselect ");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
if (TSQLHelper.ColumnIsIdentity(columnDefinition))
{
continue;
}
if (TSQLHelper.ColumnIsPrimaryKey(columnDefinition, tableDefinition))
{
continue;
}
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
result.Append($"{sep}{optionAllias}{ident}");
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
result.Append($"{Environment.NewLine}) then{Environment.NewLine}");
}
else
{
result.Append($"when matched then{Environment.NewLine}");
}
result.Append($"\tupdate set{Environment.NewLine}");
sep = null;
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
if (TSQLHelper.ColumnIsIdentity(columnDefinition))
{
continue;
}
if (TSQLHelper.ColumnIsPrimaryKey(columnDefinition, tableDefinition))
{
continue;
}
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
result.Append($"\t\t{sep}{ident} = {optionAllias}{ident}{Environment.NewLine}");
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
}
//delete
if (!option_insertonly)
{
result.Append($"when not matched by source then{Environment.NewLine}");
result.Append($"\tdelete{Environment.NewLine}");
}
if (use_output)
{
result.Append($"output $action");
sep = ", ";
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
if (!TSQLHelper.ColumnIsPrimaryKey(columnDefinition, tableDefinition, true))
{
continue;
}
result.Append($"{sep}inserted.{ident}");
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
result.Append($"{Environment.NewLine}");
result.Append($"into @mergeOutput(action");
sep = ", ";
foreach (ColumnDefinition columnDefinition in tableDefinition.ColumnDefinitions)
{
string ident = TSQLHelper.Identifier2Value(columnDefinition.ColumnIdentifier);
if (!TSQLHelper.ColumnIsPrimaryKey(columnDefinition, tableDefinition, true))
{
continue;
}
result.Append($"{sep}{ident}");
if (String.IsNullOrEmpty(sep))
{
sep = ", ";
}
}
result.Append($"){Environment.NewLine}");
}
result.Append($";{Environment.NewLine}");
return(result.ToString());
}
