SqlBulkCopy Unable to Excecute Concatenated Query

Question

INSERT INTO AssessmentDatas(ADCode, ADSetCode, ADType, ADUnit, IsExpectedValue, AnalyteResultRcadoCode, AssessmentDataApplicabilityId, AssessmentDataCalculationTypeId, AssessmentDataId, InconclusiveReason, IsInconclusive, IsReferenceRecommendationPublication, AssessmentDataLimitTypeId, MainCalculationValue, MissingVariables, RecommendationPublicationCode, ReportingGroup, SeverityLevelCode, SeverityLevelRank, CreatedBy, CreatedOnUtc, ModifiedBy, ModifiedOnUtc) 
    SELECT 
        SOURCE.ADCode, SOURCE.ADSetCode, SOURCE.ADType, SOURCE.ADUnit, SOURCE.IsExpectedValue, SOURCE.AnalyteResultRcadoCode, SOURCE.AssessmentDataApplicabilityId, SOURCE.AssessmentDataCalculationTypeId, SOURCE.AssessmentDataId, SOURCE.InconclusiveReason, SOURCE.IsInconclusive, SOURCE.IsReferenceRecommendationPublication, SOURCE.AssessmentDataLimitTypeId, SOURCE.MainCalculationValue, SOURCE.MissingVariables, SOURCE.RecommendationPublicationCode, SOURCE.ReportingGroup, SOURCE.SeverityLevelCode, SOURCE.SeverityLevelRank, SOURCE.CreatedBy, SOURCE.CreatedOnUtc, SOURCE.ModifiedBy, SOURCE.ModifiedOnUtc 
    FROM 
        dbo.#AssessmentDatas SOURCE LEFT JOIN AssessmentDatas TARGET ON SOURCE.AssessmentDataId = TARGET.AssessmentDataId 
    WHERE 
        TARGET.AssessmentDataId IS NULL;
Go
DECLARE @retries INT;
DECLARE @transactionName varchar(40);
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorNumber INT;
DECLARE @hasLock bit = 0;
SET @retries = 3;
WHILE (@retries >= 0)
BEGIN
 SET @retries = @retries - 1;
    SET @hasLock = 0;
    BEGIN TRY
        SET @transactionName = CAST(NEWID() AS VARCHAR(40));
        BEGIN TRANSACTION @transactionName
        SET LOCK_TIMEOUT 1000;
        SET @hasLock = 1;
        UPDATE TARGET 
SET 
    TARGET.ADCode = SOURCE.ADCode, TARGET.ADSetCode = SOURCE.ADSetCode, TARGET.ADType = SOURCE.ADType, TARGET.ADUnit = SOURCE.ADUnit, TARGET.IsExpectedValue = SOURCE.IsExpectedValue
FROM 
    dbo.#AssessmentDatas SOURCE JOIN AssessmentDatas TARGET ON SOURCE.AssessmentDataId = TARGET.AssessmentDataId WHERE (((SOURCE.ADCode IS NULL AND TARGET.ADCode IS NOT NULL) OR (SOURCE.ADCode IS NOT NULL AND TARGET.ADCode IS NULL) OR (SOURCE.ADCode <> TARGET.ADCode)) OR ((SOURCE.ADSetCode IS NULL AND TARGET.ADSetCode IS NOT NULL)  
        COMMIT TRANSACTION @transactionName
        SET @retries = -1;
    END TRY
    BEGIN CATCH            
        SELECT  @ErrorMessage = N'Unable to complete write operation. Error Number:' + CAST(ISNULL(ERROR_NUMBER(),-1) AS NVARCHAR(10)) + '.DB Error Message:' + ERROR_MESSAGE(),
                @ErrorSeverity = ERROR_SEVERITY(),
                @ErrorState = ERROR_STATE();

        IF(@hasLock = 1)
        BEGIN
            ROLLBACK TRANSACTION @transactionName
        END

        IF (@retries = 0)
        BEGIN
            RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
        END
 WAITFOR DELAY '00:00:00.050'
    END CATCH;
END;
Go
DROP TABLE dbo.#AssessmentDatas
Go
INSERT INTO ADLimits(AssessmentDataId, ADLimitId, Level, Value, CreatedBy, CreatedOnUtc, ModifiedBy, ModifiedOnUtc) 
    SELECT 
        SOURCE.AssessmentDataId, SOURCE.ADLimitId, SOURCE.Level, SOURCE.Value, SOURCE.CreatedBy, SOURCE.CreatedOnUtc, SOURCE.ModifiedBy, SOURCE.ModifiedOnUtc 
    FROM 
        dbo.#ADLimits SOURCE LEFT JOIN ADLimits TARGET ON SOURCE.ADLimitId = TARGET.ADLimitId 
    WHERE 
        TARGET.ADLimitId IS NULL;
Go
DECLARE @retries INT;
DECLARE @transactionName varchar(40);
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorNumber INT;
DECLARE @hasLock bit = 0;
SET @retries = 3;
WHILE (@retries >= 0)
BEGIN
 SET @retries = @retries - 1;
    SET @hasLock = 0;
    BEGIN TRY
        SET @transactionName = CAST(NEWID() AS VARCHAR(40));
        BEGIN TRANSACTION @transactionName
        SET LOCK_TIMEOUT 1000;
        SET @hasLock = 1;
        UPDATE TARGET 
SET 
    TARGET.AssessmentDataId = SOURCE.AssessmentDataId, TARGET.Level = SOURCE.Level, TARGET.Value = SOURCE.Value, TARGET.CreatedBy = SOURCE.CreatedBy, TARGET.CreatedOnUtc = SOURCE.CreatedOnUtc, TARGET.ModifiedBy = SOURCE.ModifiedBy, TARGET.ModifiedOnUtc = SOURCE.ModifiedOnUtc 
FROM 
    dbo.#ADLimits SOURCE JOIN ADLimits TARGET ON SOURCE.ADLimitId = TARGET.ADLimitId WHERE (((SOURCE.AssessmentDataId IS NULL AND TARGET.AssessmentDataId IS NOT NULL) OR (SOURCE.AssessmentDataId IS NOT NULL AND TARGET.AssessmentDataId IS NULL) OR (SOURCE.AssessmentDataId <> TARGET.AssessmentDataId)) OR ((SOURCE.Level IS NULL AND TARGET.Level IS NOT NULL) OR (SOURCE.Level IS NOT NULL AND TARGET.Level IS NULL) OR (SOURCE.Level <> TARGET.Level)) OR ((SOURCE.Value IS NULL AND TARGET.Value IS NOT NULL) OR (SOURCE.Value IS NOT NULL AND TARGET.Value IS NULL) OR (SOURCE.Value <> TARGET.Value)) OR ((SOURCE.CreatedBy IS NULL AND TARGET.CreatedBy IS NOT NULL) OR (SOURCE.CreatedBy IS NOT NULL AND TARGET.CreatedBy IS NULL) OR (SOURCE.CreatedBy <> TARGET.CreatedBy)) OR ((SOURCE.CreatedOnUtc IS NULL AND TARGET.CreatedOnUtc IS NOT NULL) OR (SOURCE.CreatedOnUtc IS NOT NULL AND TARGET.CreatedOnUtc IS NULL) OR (SOURCE.CreatedOnUtc <> TARGET.CreatedOnUtc))) AND SOURCE.ModifiedBy IS NOT NULL
        COMMIT TRANSACTION @transactionName
        SET @retries = -1;
    END TRY
    BEGIN CATCH            
        SELECT  @ErrorMessage = N'Unable to complete write operation. Error Number:' + CAST(ISNULL(ERROR_NUMBER(),-1) AS NVARCHAR(10)) + '.DB Error Message:' + ERROR_MESSAGE(),
                @ErrorSeverity = ERROR_SEVERITY(),
                @ErrorState = ERROR_STATE();

        IF(@hasLock = 1)
        BEGIN
            ROLLBACK TRANSACTION @transactionName
        END

        IF (@retries = 0)
        BEGIN
            RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
        END
 WAITFOR DELAY '00:00:00.050'
    END CATCH;
END;
Go
DROP TABLE dbo.#ADLimits

individual its working

   var  finalQuerySb = insertQuery1 +"Go" + updateQuery1  + dropQuery1;

ForEach Loop Concatenation not Working

            foreach (var tuple in results)
            {
                selectQuery.Append($"SELECT TOP(0) * INTO {tuple.tempTableName} FROM {tuple.tableName};");
                var updateQuery = DataTableHelper.GetUpdateQuery(tuple.type, tuple.tempTableName, tuple.tableName);
                var insertQuery = DataTableHelper.GetInsertQuery(tuple.type, tuple.tempTableName, tuple.tableName);
                var dropQuery = $"DROP TABLE {tuple.tempTableName}";
                mergerQuery.Append(insertQuery + "
Go
" + updateQuery + "
Go
"  + dropQuery + "
Go
");
                query1.Add(insertQuery + updateQuery +   dropQuery  );

              //  tableName.Add(tuple.tempTableName);
                tuples.Add((tuple.dt, tuple.tempTableName));
            }



  var updateCommand = transaction != null ? new SqlCommand(query[1], destinationConnection, transaction as SqlTransaction) : new SqlCommand(query[1], destinationConnection);
                updateCommand.CommandType = CommandType.Text;

                await updateCommand.ExecuteNonQueryAsync();

Tried with "Go" and without "GO"

Answer

GO is not an SQL statement. GO is something you use in a query tool like SQL Server Management Studio. GO is an instruction to the query tool to split up the script in batches. So if the script goes:

DECLARE @x int = 12
SELECT @x
go
DECLARE @x int = 243
SELECT @x

SSMS will first send the first two lines to SQL Server and wait for response. Once it has received the result it will send lines 4 and 5 in the script. Note that the line that reads go is never sent to SQL Server.

When you write an application, it is a different thing. In this case, you need to manage the batches yourself. If you are reading a script from disk, you need to parse out go yourself. If you have the code inside your application, which you seem to have, you don't use go at all. And if you are concatenating different parts, you need to make sure that they can execute as a single batch. A starting point here is that the a variable can only be declared once in the same batch.

I'm inclined to suggest, though, that in your case, you are probably better of putting this code in a stored procedure or two, and call this procedure from your client code. Those batches are a tad long to embed in to C# and it will be difficult to maintain the code, in my opinion.

Answer

I don't really see where SqlBulkCopy comes in here. With SqlBulkCopy, you don't write any queries. You specify the table to insert into. If you also want to Update, SqlBulkCopy is not really your friend.

As for the compilation errors that the variables have been declared, you will of course need to remove the duplicate variable declarations.

But as I said yesterday, a stored procedure may be a better path to take.

SqlBulkCopy Unable to Excecute Concatenated Query

2 answers