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.

"I do not know the Id if one isn't created". Isn't it just the ID you pass in? You pass in an id (I assume this is `@Id` and if that is found, then _one isn't created_ — Nick.Mc, Jan 26 '16 at 22:15
I mean what language is the construct `conn.ExecuteScalar(` from? I don't recognize it. You should tag your question with the technologies involved here. — TT., Jan 27 '16 at 10:40
Dapper is just a mechanism here; the ultimate question would be the same when using raw ado.net; so; what do you *expect* `@Id` to be in each case? — Marc Gravell, Jan 27 '16 at 12:51
Do you mean 'if one _is_ created' (in the database) rather than _isn't_? You can use the `OUTPUT` clause in a `MERGE` statement. Here's an example: http://stackoverflow.com/questions/13620044/how-to-use-output-to-capture-new-and-old-id — Nick.Mc, Jan 28 '16 at 05:12

score 0 · Answer 1 · answered Jan 26 '16 at 21:53

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).

Generate an Id (GUID) from a Merge Statement

1 Answers1