CODEWITHSUNDEEP
c#sql-servermerge-statement
Yuvi
Yuvi

Reputation: 640

My Merge statement is failing

I have a merge statement which is to insert values into a database. I hit an exception point with an exception error

A MERGE statement must be terminated by a semi-colon (;).

Here is my statement. Could anyone point out what am I missing please?

using (var masterDb  = new SqlConnection(ConfigurationManager.ConnectionStrings["MasterDB"].ConnectionString))
{
    masterDb.Open();
    if (data != null)
        try
        {
            conn.Execute(
                "MERGE INTO [dbo].[FormSubmissions] AS TARGET " +
                "USING(VALUES(@Id,@CreatedAt,@UpdatedAt,@IsComplete,@FormId,[@Source],@SentEmail)) AS SOURCE (Id,CreatedAt,UpdatedAt,IsComplete,FormId,[Source],SentEmail) " +
                "ON TARGET.Id = SOURCE.Id " +
                "WHEN MATCHED THEN " +
                "UPDATE SET CreatedAt = SOURCE.CreatedAt,UpdatedAt = SOURCE.UpdatedAt,IsComplete = SOURCE.IsComplete, FormId = SOURCE.FormId, [Source] = SOURCE.[Source], SentEmail = SOURCE.SentEmail " +
                "WHEN NOT MATCHED BY TARGET THEN " +
                "INSERT(Id,CreatedAt,UpdatedAt,IsComplete,FormId,[Source],SentEmail) " +
                "VALUES(Id,CreatedAt,UpdatedAt,IsComplete,FormId,[Source],SentEmail)",
                new
                {
                    Id = formSubmissionsId,
                    CreatedAt = data.CreatedAt,
                    UpdatedAt = data.UpdatedAt,
                    IsComplete = data.IsComplete,
                    FormId = data.FormId,
                    Source = data.Source,
                    SentEmail = data.SentEmail
                });
        }
        catch (Exception ex)
        {
            throw;   
        }
        finally
        {
            masterDb.Close();
        }
};
conn.Close();
}

Upvotes: 1

Views: 751

Answers (1)

jradich1234
jradich1234

Reputation: 1425

As the error says add a semicolon to the end of your statement. So your Execute should look like the following.

conn.Execute(
     "MERGE INTO [dbo].[FormSubmissions] AS TARGET " +
     "USING(VALUES(@Id,@CreatedAt,@UpdatedAt,@IsComplete,@FormId,[@Source],@SentEmail)) AS SOURCE (Id,CreatedAt,UpdatedAt,IsComplete,FormId,[Source],SentEmail) " +
     "ON TARGET.Id = SOURCE.Id " +
     "WHEN MATCHED THEN " +
     "UPDATE SET CreatedAt = SOURCE.CreatedAt,UpdatedAt = SOURCE.UpdatedAt,IsComplete = SOURCE.IsComplete, FormId = SOURCE.FormId,  [Source] = SOURCE.[Source], SentEmail = SOURCE.SentEmail " +
     "WHEN NOT MATCHED BY TARGET THEN " +
     "INSERT(Id,CreatedAt,UpdatedAt,IsComplete,FormId,[Source],SentEmail) " +
     "VALUES(Id,CreatedAt,UpdatedAt,IsComplete,FormId,[Source],SentEmail);",
new
    {
      Id = formSubmissionsId,
      CreatedAt = data.CreatedAt,
      UpdatedAt = data.UpdatedAt,
      IsComplete = data.IsComplete,
      FormId = data.FormId,
      Source = data.Source,
      SentEmail = data.SentEmail
    });

Upvotes: 2

Related Questions