Generate an Id (GUID) from a Merge Statement

Question

I have a data repository layer which accesses an SQL table on our database called Form.

I am trying to write a Merge statement which inserts a new Id which is a GUID and updates the record if there is already an Id. However, my issue is that I do not know the Id if one isn't created.

I cannot get my head around it. This has made me thinking whether my statement would actually work at all.

This is my code:

conn.ExecuteScalar<Guid>(
"MERGE INTO [dbo].[Form] AS TARGET USING(VALUES(@Id,@CreatedAt,@IsComplete,@Data)) AS SOURCE(Id,CreatedAt,IsComplete,[Data]) " + 
"ON TARGET.Id = SOURCE.Id WHEN MATCHED THEN " +
"UPDATE SET CreatedAt = SOURCE.CreatedAt,IsComplete = SOURCE.IsComplete, [Data] = SOURCE.[Data] " +
"WHEN NOT MATCHED BY TARGET THEN " +
"INSERT(Id,CreatedAt,IsComplete,[Data]) " +
"VALUES(newId(),CreatedAt,IsComplete,[Data]) OUTPUT INSERTED.Id " +
"new{Id = ??????, CreatedAt = enquiry.EnquiryDate, IsComplete = 1, Data = doc});

I am not sure what to put in the New for Id (I left it with ???). The enquiry is an object which contains some data from another table and the doc is an XML document.

Any suggestions on this would be a great help.

Answer 1

Consider moving the responsibility of creating a new GUID to your repository instead of having SQL Server create it for you.

Hopefully in your repository you know whether you have a ID for the record you are working with or whether it is a new document that does not yet have an ID assigned.

In the event that you do not have an ID for the current document, use C# to create a new GUID for you. From here on out the code would be the same regardless of whether you are editing an existing record or creating a new record: You simply pass the ID variable to the SqlCommand (ideally as a Parameter).

In your WHEN NOT MATCHED statement you can simply refer to SOURCE.Id instead of using newId().

Since you created the GUID in the repository, you already know what the value is should you need to use it as part of another operation (no need to return it from SQL Server).