in src/Core/Resolvers/MsSqlQueryBuilder.cs [74:188]
public string Build(SqlInsertStructure structure)
{
SourceDefinition sourceDefinition = structure.GetUnderlyingSourceDefinition();
bool isInsertDMLTriggerEnabled = sourceDefinition.IsInsertDMLTriggerEnabled;
string tableName = $"{QuoteIdentifier(structure.DatabaseObject.SchemaName)}.{QuoteIdentifier(structure.DatabaseObject.Name)}";
// Predicates by virtue of database policy for Create action.
string dbPolicypredicates = JoinPredicateStrings(structure.GetDbPolicyForOperation(EntityActionOperation.Create));
// Columns whose values are provided in the request body - to be inserted into the record.
string insertColumns = Build(structure.InsertColumns);
// Values to be inserted into the entity.
string values = dbPolicypredicates.Equals(BASE_PREDICATE) ?
$"VALUES ({string.Join(", ", structure.Values)});" : $"SELECT {insertColumns} FROM (VALUES({string.Join(", ", structure.Values)})) T({insertColumns}) WHERE {dbPolicypredicates};";
// Final insert query to be executed against the database.
StringBuilder insertQuery = new();
if (!isInsertDMLTriggerEnabled)
{
if (!string.IsNullOrEmpty(insertColumns))
{
// When there is no DML trigger enabled on the table for insert operation, we can use OUTPUT clause to return the data.
insertQuery.Append($"INSERT INTO {tableName} ({insertColumns}) OUTPUT " +
$"{MakeOutputColumns(structure.OutputColumns, OutputQualifier.Inserted.ToString())} ");
insertQuery.Append(values);
}
else
{
insertQuery.Append($"INSERT INTO {tableName} OUTPUT " +
$"{MakeOutputColumns(structure.OutputColumns, OutputQualifier.Inserted.ToString())} DEFAULT VALUES");
}
}
else
{
// When a DML trigger for INSERT operation is enabled on the table, it's a bit tricky to get the inserted data.
// We need to insert the values for all the non-autogenerated PK columns into a temporary table.
// Finally this temporary table will be used to do a subsequent select on the actual table where we would join the
// actual table and the temporary table based on the values of the non-autogenerated PK columns.
// If there is a column in the PK which is autogenerated, we cannot and will not insert it into the temporary table.
// Hence in the select query, for the autogenerated PK field, an additional WHERE predicate is added to fetch the unique record
// by extracting the value of the autogenerated PK field using the SCOPE_IDENTITY() method provided by Sql Server.
// It is to be noted that MsSql supports only one IDENTITY/autogenerated column per table.
string tempTableName = $"{QuoteIdentifier(structure.DatabaseObject.SchemaName)}.{QuoteIdentifier($"#{structure.DatabaseObject.Name}_T")}";
(string autoGenPKColumn, List<string> nonAutoGenPKColumns) = GetSegregatedPKColumns(sourceDefinition);
if (nonAutoGenPKColumns.Count > 0)
{
// Create temporary table containing zero rows and all the non-autogenerated columns present in the PK.
// We need to create it only when there are non-autogenerated columns present in the PK as column names.
// Using a 'WHERE 0=1' predicate ensures that we are not requesting back (and inserting) any additional data and hence no additional resources are consumed.
string queryToCreateTempTable = $"SELECT {string.Join(", ", nonAutoGenPKColumns.Select(pk => $"{QuoteIdentifier(pk)}"))}" +
$" INTO {tempTableName} FROM {tableName} WHERE 0 = 1;";
// We need to output values of all the non-autogenerated columns in the PK into the temporary table.
string nonAutoGenPKsOutput = string.Join(", ", nonAutoGenPKColumns.Select(pk => $"{OutputQualifier.Inserted}.{QuoteIdentifier(pk)}"));
// Creation of temporary table followed by inserting data into actual table.
insertQuery.Append(queryToCreateTempTable);
insertQuery.Append($"INSERT INTO {tableName} ({insertColumns}) ");
insertQuery.Append($"OUTPUT {nonAutoGenPKsOutput} INTO {tempTableName} ");
}
else
{
insertQuery.Append($"INSERT INTO {tableName} ({insertColumns}) ");
}
insertQuery.Append(values);
// Build the subsequent select query to return the inserted data. By the time the subsequent select executes,
// the trigger would have already executed and we get the data as it is present in the table.
StringBuilder subsequentSelect = new($"SELECT {MakeOutputColumns(structure.OutputColumns, tableName)} FROM {tableName} ");
if (nonAutoGenPKColumns.Count > 0)
{
// We will perform inner join on the basis of all the non-autogenerated columns in the PK.
string joinPredicates = string.Join(
"AND ",
nonAutoGenPKColumns.Select(pk => $"{tableName}.{QuoteIdentifier(pk)} = {tempTableName}.{QuoteIdentifier(pk)}"));
subsequentSelect.Append($"INNER JOIN {tempTableName} ON {joinPredicates} ");
}
if (!string.IsNullOrEmpty(autoGenPKColumn))
{
// If there is an autogenerated column in the PK, we will add an additional WHERE condition for it.
// Using SCOPE_IDENTITY() method provided by sql server,
// we can get the last generated value of the autogenerated column.
subsequentSelect.Append($"WHERE {tableName}.{QuoteIdentifier(autoGenPKColumn)} = SCOPE_IDENTITY()");
}
insertQuery.Append(subsequentSelect.ToString());
// Since we created a temporary table, it will be dropped automatically as the session terminates.
// So, we don't need to explicitly drop the temporary table.
insertQuery.Append(";");
/* An example final insert query with trigger would look something like:
* -- Creation of temporary table
* SELECT [nonautogen_id] INTO [dbo].[#table_T] FROM [dbo].[table] WHERE 0 = 1;
* -- Insertion of values into the actual table
* INSERT INTO [dbo].[table] ([field1], [field2], [field3]) OUTPUT Inserted.[nonautogen_id] INTO [dbo].[#table_T]
* -- Values to insert into the table.
* VALUES(@param1, @param2, @param3);
* -- Subsequent select query to get back data.
* SELECT [dbo].[table].[id] AS [id], [dbo].[table].[nonautogen_id] AS [nonautogen_id], [dbo].[table].[field1] AS [field1], [dbo].[table].[field2] AS [field2], [dbo].[table].[field3] AS [field3]
* FROM [dbo].[table]
* -- INNER JOIN for non-autogen PK field
* INNER JOIN [dbo].[#table_T] ON [dbo].[table].[nonautogen_id] = [dbo].[#table_T].[nonautogen_id]
* -- WHERE clause for autogen PK field
* WHERE [dbo].[table].[autogen_id] = SCOPE_IDENTITY();
*/
}
return insertQuery.ToString();
}