public virtual SqlMergeScriptResults BuildSqlMergeScripts(
SqlBulkHelpersTableDefinition tableDefinition,
SqlBulkHelpersMergeAction mergeAction,
SqlMergeMatchQualifierExpression matchQualifierExpression = null
)
{
//NOTE: BBernard - This temp table name MUST begin with 1 (and only 1) hash "#" to ensure it is a Transaction Scoped table!
var tempStagingTableName = $"#SqlBulkHelpers_STAGING_TABLE_{Guid.NewGuid()}";
var tempOutputIdentityTableName = $"#SqlBulkHelpers_OUTPUT_IDENTITY_TABLE_{Guid.NewGuid()}";
var identityColumnName = tableDefinition.IdentityColumn?.ColumnName ?? String.Empty;
var columnNamesListWithoutIdentity = tableDefinition.GetColumnNames(false);
var columnNamesWithoutIdentityCSV = columnNamesListWithoutIdentity.Select(c => $"[{c}]").ToCSV();
//Dynamically build the Merge Match Qualifier Fields Expression
//NOTE: This is an optional parameter now, but is initialized to the IdentityColumn by Default!
var qualifierExpression = matchQualifierExpression ?? new SqlMergeMatchQualifierExpression(identityColumnName);
var mergeMatchQualifierExpressionText = BuildMergeMatchQualifierExpressionText(qualifierExpression);
//Initialize/Create the Staging Table!
//NOTE: THe ROWNUMBER_COLUMN_NAME (3'rd Column) IS CRITICAL because SqlBulkCopy and Sql Server OUTPUT claus do not
// preserve Order; e.g. it may change based on execution plan (indexes/no indexes, etc.).
String sqlScriptToInitializeTempTables = $@"
SELECT TOP(0)
-1 as [{identityColumnName}],
{columnNamesWithoutIdentityCSV},
-1 as [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}]
INTO [{tempStagingTableName}]
FROM [{tableDefinition.TableName}];
ALTER TABLE [{tempStagingTableName}] ADD PRIMARY KEY ([{identityColumnName}]);
SELECT TOP(0)
CAST('' AS nvarchar(10)) as [MERGE_ACTION],
CAST(-1 AS int) as [IDENTITY_ID],
CAST(-1 AS int) [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}]
INTO [{tempOutputIdentityTableName}];
";
//NOTE: This is ALL now completed very efficiently on the Sql Server Database side with
// NO unnecessary round trips to the Database!
var mergeInsertSql = String.Empty;
if (mergeAction.HasFlag(SqlBulkHelpersMergeAction.Insert))
{
mergeInsertSql = $@"
WHEN NOT MATCHED BY TARGET THEN
INSERT ({columnNamesWithoutIdentityCSV})
VALUES ({columnNamesListWithoutIdentity.Select(c => $"source.[{c}]").ToCSV()})
";
}
var mergeUpdateSql = String.Empty;
if (mergeAction.HasFlag(SqlBulkHelpersMergeAction.Update))
{
mergeUpdateSql = $@"
WHEN MATCHED THEN
UPDATE SET {columnNamesListWithoutIdentity.Select(c => $"target.[{c}] = source.[{c}]").ToCSV()}
";
}
//Build the FULL Dynamic Merge Script here...
//BBernard - 2019-08-07
//NOTE: We now sort on the RowNumber column that we define; this FIXES issue with SqlBulkCopy.WriteToServer()
// where the order of data being written is NOT guaranteed, and there is still no support for the ORDER() hint.
// In general it results in inverting the order of data being sent in Bulk which then resulted in Identity
// values being incorrect based on the order of data specified.
//NOTE: We MUST SORT the OUTPUT Results by ROWNUMBER and then by IDENTITY Column in case there are multiple matches due to
// custom match Qualifiers; this ensures that data is sorted in a way that postprocessing
// can occur & be validated as expected.
String sqlScriptToExecuteMergeProcess = $@"
MERGE [{tableDefinition.TableName}] as target
USING (
SELECT TOP 100 PERCENT *
FROM [{tempStagingTableName}]
ORDER BY [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}] ASC
) as source
ON {mergeMatchQualifierExpressionText}
{mergeUpdateSql}
{mergeInsertSql}
OUTPUT $action, INSERTED.[{identityColumnName}], source.[{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}]
INTO [{tempOutputIdentityTableName}] ([MERGE_ACTION], [IDENTITY_ID], [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}]);
SELECT
[{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}],
[IDENTITY_ID],
[MERGE_ACTION]
FROM [{tempOutputIdentityTableName}]
ORDER BY [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}] ASC, [IDENTITY_ID] ASC;
DROP TABLE [{tempStagingTableName}];
DROP TABLE [{tempOutputIdentityTableName}];
";
return(new SqlMergeScriptResults(
tempStagingTableName,
tempOutputIdentityTableName,
sqlScriptToInitializeTempTables,
sqlScriptToExecuteMergeProcess,
qualifierExpression
));
}
public SqlMergeScriptResults BuildSqlMergeScripts(SqlBulkHelpersTableDefinition tableDefinition, SqlBulkHelpersMergeAction mergeAction)
{
//NOTE: BBernard - This temp table name MUST begin with 1 (and only 1) hash "#" to ensure it is a Transaction Scoped table!
var tempStagingTableName = $"#SqlBulkHelpers_STAGING_TABLE_{Guid.NewGuid()}";
var tempOutputIdentityTableName = $"#SqlBulkHelpers_OUTPUT_IDENTITY_TABLE_{Guid.NewGuid()}";
var identityColumnName = tableDefinition.IdentityColumn?.ColumnName ?? String.Empty;
var columnNamesListWithoutIdentity = tableDefinition.GetColumnNames(false);
var columnNamesWithoutIdentityCSV = columnNamesListWithoutIdentity.Select(c => $"[{c}]").ToCSV();
//Initialize/Create the Staging Table!
String sqlScriptToInitializeTempTables = $@"
SELECT TOP(0)
-1 as [{identityColumnName}],
{columnNamesWithoutIdentityCSV},
-1 as [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}]
INTO [{tempStagingTableName}]
FROM [{tableDefinition.TableName}];
ALTER TABLE [{tempStagingTableName}] ADD PRIMARY KEY ([{identityColumnName}]);
SELECT TOP(0)
CAST('' AS nvarchar(10)) as [MERGE_ACTION],
CAST(-1 AS int) as [IDENTITY_ID],
CAST(-1 AS int) [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}]
INTO [{tempOutputIdentityTableName}];
";
//NOTE: This is ALL now completed very efficiently on the Sql Server Database side with
// NO unnecessary round trips to the Database!
var mergeInsertSql = String.Empty;
if (mergeAction.HasFlag(SqlBulkHelpersMergeAction.Insert))
{
mergeInsertSql = $@"
WHEN NOT MATCHED BY TARGET THEN
INSERT ({columnNamesWithoutIdentityCSV})
VALUES ({columnNamesListWithoutIdentity.Select(c => $"source.[{c}]").ToCSV()})
";
}
var mergeUpdateSql = String.Empty;
if (mergeAction.HasFlag(SqlBulkHelpersMergeAction.Update))
{
mergeUpdateSql = $@"
WHEN MATCHED THEN
UPDATE SET {columnNamesListWithoutIdentity.Select(c => $"target.[{c}] = source.[{c}]").ToCSV()}
";
}
//Build the FULL Dynamic Merge Script here...
//BBernard - 2019-08-07
//NOTE: We now sort on the RowNumber column that we define; this FIXES issue with SqlBulkCopy.WriteToServer()
// where the order of data being written is NOT guaranteed, and there is still no support for the ORDER() hint.
// In general it results in inverting the order of data being sent in Bulk which then resulted in Identity
// values being incorrect based on the order of data specified.
String sqlScriptToExecuteMergeProcess = $@"
MERGE [{tableDefinition.TableName}] as target
USING (
SELECT TOP 100 PERCENT *
FROM [{tempStagingTableName}]
ORDER BY [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}] ASC
) as source
ON target.[{identityColumnName}] = source.[{identityColumnName}]
{mergeUpdateSql}
{mergeInsertSql}
OUTPUT $action, INSERTED.[{identityColumnName}], source.[{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}]
INTO [{tempOutputIdentityTableName}] ([MERGE_ACTION], [IDENTITY_ID], [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}]);
SELECT
[{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}],
[IDENTITY_ID],
[MERGE_ACTION]
FROM [{tempOutputIdentityTableName}]
ORDER BY [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}] ASC;
DROP TABLE [{tempStagingTableName}];
DROP TABLE [{tempOutputIdentityTableName}];
";
return(new SqlMergeScriptResults(
tempStagingTableName,
tempOutputIdentityTableName,
sqlScriptToInitializeTempTables,
sqlScriptToExecuteMergeProcess
));
}