Execute and insert/upsert mutation against MS SQL Server. See the documentation for buildInsertTx to see how it's done.

Translates an IR Insert/upsert mutation description to SQL and builds a corresponding transaction to run against MS SQL Server.

Execution of a MSSQL insert mutation broadly involves two steps.

insert_table(objects: [
  {column1: value1, column2: value2},
  {column1: value3, column2: value4}
 ],
 if_matched: {match_columns: [column1], update_columns: [column2]} # Optional field to enable upserting
){
  affected_rows
  returning {
    column1
    column2
  }
}

Step 1: Inserting rows into the table

a. Create an empty temporary table with name #inserted to store affected rows (for the response)

SELECT column1, column2 INTO #inserted FROM some_table WHERE (1 <> 1)
UNION ALL SELECT column1, column2 FROM some_table WHERE (1 <> 1);

b. If if_matched is found: Use MERGE statment to perform upsert

b.1 Use #values temporary table to store input object values

SELECT column1, column2 INTO #values FROM some_table WHERE (1 <> 1)

b.2 Insert input object values into the temporary table

INSERT INTO #values (column1, column2) VALUES (value1, value2), (value3, value4)

b.3 Generate an SQL Merge statement to perform either update or insert (upsert) to the table

MERGE some_table AS [target]
USING (SELECT column1, column2 from #values) AS [source](column1, column2) ON ([target].column1 = [source].column1)
WHEN MATCHED THEN UPDATE SET [column2] = [source].[column2]
WHEN NOT MATCHED THEN INSERT (column1, column2) VALUES ([source].column1, [source].column2)
OUTPUT INSERTED.column1, INSERTED.column2 INTO #inserted(column1, column2)

NOTE: In MERGE statement, we use SELECT query from a temporary table as source but not VALUES expression because, we can't use DEFAULT expression (for missing columns in objects field) in VALUES expression.

else: Generate an SQL Insert statement from the GraphQL insert mutation with OUTPUT expression to fill #inserted temporary table with inserted rows

INSERT INTO some_table (column1, column2) OUTPUT INSERTED.column1, INSERTED.column2 INTO #inserted(column1, column2) VALUES (value1, value2), (value3, value4);

Step 2: Generation of the mutation response

An SQL statement is generated and when executed it returns the mutation selection set containing affected_rows and returning field values. The statement is generated with multiple sub select queries explained below:

a. A SQL Select statement to fetch only inserted rows from temporary table

<table_select> := SELECT * FROM #inserted

The above select statement is referred through a common table expression - WITH [with_alias] AS (table_select)

b. The affected_rows field value is obtained by using COUNT aggregation and the returning field selection set is translated to a SQL select statement using mkSQLSelect.

<mutation_output_select> :=
  SELECT (SELECT COUNT(*) FROM [with_alias]) AS [affected_rows], (select_from_returning) AS [returning]
  FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

c. Evaluate the check constraint using CASE expression. We use SUM aggregation to check if any inserted row has failed the check constraint.

<check_constraint_select> :=
  SELECT SUM([check_sub_query].[check_evaluation])
  FROM
    ( SELECT
        (CASE WHEN <check_boolean_expression> THEN 0 ELSE 1 END) AS [check_evaluation]
      FROM
        [with_alias]
    ) AS [check_sub_query]

d. The final select statement look like

WITH "with_alias" AS (<table_select>)
SELECT (<mutation_output_select>) AS [mutation_response], (<check_constraint_select>) AS [check_constraint_select]

When executed, the above statement returns a single row with mutation response as a string value and check constraint result as an integer value.

Translates an IR IfMatched clause to SQL and builds a corresponding transaction to run against MS SQL Server.

We do this in 2 steps:

1. Create a temporary table called #values which will hold the values the user want to insert, and insert the values into it
2. Build an run a MERGE statement to either insert or upsert the values from the temporary table #values into the original table, and output the affected rows into another temporary table called #inserted which will be used to build a "response" for the user.

Should be used as part of a bigger transaction in buildInsertTx.

Builds a response to the user using the values in the temporary table named #inserted.