Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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());
        }
Exemplo n.º 3
0
        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());
        }
Exemplo n.º 7
0
        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());
        }