0

I would like to know if a MERGE statement could, somehow, be written to support value sets with different column counts?

First, if we had a table that is defined as follows:

CREATE TABLE [Example]
(
  [Id] NVARCHAR(10) NOT NULL,
  [Name] NVARCHAR(100) NOT NULL,
  [Format] NVARCHAR(MAX) DEFAULT('yyyy-MM-dd')
)

Is it somehow possible to create a single MERGE statement which supports entries with and without the format column? Can the following be adjusted to work?

MERGE [Example] AS t
USING
(
  VALUES
  ('id1', 'England', 'dd/MM/yyyy'),
  ('id2', 'Germany'), -- The expectation is that the default value would be used
  ('id3', 'America', 'MM.dd.yyyy')
) AS s([Id], [Name], [Format])
ON
  s.[Id] = t.[Id]
WHEN MATCHED THEN
  UPDATE SET
    t.[Name] = s.[Name],
    t.[Format] = s.[Format]
WHEN NOT MATCHED THEN
  INSERT ([Id], [Name], [Format])
    VALUES (s.[Id], s.[Name], s.[Format]);

This doesn't work because the Germany entry only has 2 values, which does not match the defined column count. I've tried adding ('id2', 'Germany', NULL) but that inserts a NULL and not the default value defined for the column. I've also tried adding ('id2', 'Germany', DEFAULT) but that failed with a Syntax error.

Is what I'm after possible? The only solution I have so far is to maintain two separte MERGE statements, one with the Format column, one without - is there any way to bring them together?

6
  • 1
    No - according to the documentation you can only supply DEFAULT keyword for an insert not merge. You know the default value though so just use it in your values constructor? Commented Jun 18, 2022 at 19:01
  • sqlblog.org/merge Commented Jun 18, 2022 at 19:01
  • Hi @AaronBertrand interesting links - I haven't read them all, but what I did look at was interesting. As far as the locking/racing issues, this MERGE will only be run during DB installs (or updates), so all other processes will be offline. Commented Jun 18, 2022 at 20:49
  • Hi @Stu, the final MERGE has over 200 entries, so I was hoping to avoid having to provide the same value over and over again... Commented Jun 18, 2022 at 20:51
  • Then perhaps use separate update and insert steps where you are able to use the default in a values constructor? Commented Jun 18, 2022 at 20:54

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.