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?
insertnotmerge. You know the default value though so just use it in your values constructor?