/// <summary> /// /// </summary> /// <param name="tableName"></param> /// <param name="tempTableName"></param> /// <param name="fields"></param> /// <param name="identityField"></param> /// <param name="hints"></param> /// <param name="dbSetting"></param> /// <param name="isReturnIdentity"></param> /// <returns></returns> private static string GetBulkInsertSqlText(string tableName, string tempTableName, IEnumerable <Field> fields, Field identityField, string hints, IDbSetting dbSetting, bool isReturnIdentity) { // Validate the presence if (fields?.Any() != true) { throw new MissingFieldException("There are no field(s) defined."); } // Variables needed var builder = new QueryBuilder(); // Insertable fields var insertableFields = fields .Where(field => string.Equals(field.Name, identityField?.Name, StringComparison.OrdinalIgnoreCase) == false); // Compose the statement builder.Clear() // MERGE T USING S .Merge() .TableNameFrom(tableName, dbSetting) .HintsFrom(hints) .As("T") .Using() .OpenParen() .Select() .Top() .WriteText("100 PERCENT") //.FieldsFrom(fields, dbSetting) .WriteText("*") // Including the [__RepoDb_OrderColumn] .From() .TableNameFrom(tempTableName, dbSetting); // Return Identity if (isReturnIdentity && identityField != null) { builder .OrderBy() .WriteText("[__RepoDb_OrderColumn]") .Ascending(); } // Continuation builder .CloseParen() .As("S") // QUALIFIERS .On() .OpenParen() .WriteText("1 = 0") .CloseParen() // WHEN NOT MATCHED THEN INSERT VALUES .When() .Not() .Matched() .Then() .Insert() .OpenParen() .FieldsFrom(insertableFields, dbSetting) .CloseParen() .Values() .OpenParen() .AsAliasFieldsFrom(insertableFields, "S", dbSetting) .CloseParen(); // Set the output if (isReturnIdentity == true && identityField != null) { builder .WriteText(string.Concat("OUTPUT INSERTED.", identityField.Name.AsField(dbSetting))) .As("[Result],") .WriteText("S.[__RepoDb_OrderColumn]") .As("[OrderColumn]"); } // End builder.End(); // Return the sql return(builder.ToString()); }