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