/// <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());
}
