Sql-server – sqlserver 2014 DEFAULT in VALUES using MERGE

clojuresql server 2014

I'm trying to insert or update multiple rows in an SQL Server 2014 database. Some of the columns may need to be set to DEFAULT, however sqlserver barfs when there is a DEFAULT. Incorrect syntax near the keyword 'DEFAULT'.

Here is a small representative example of a query. The real query is actually being generated using clojure's hugsql library, using data generated in the clojure program.

MERGE INTO [dbo].[IMOS] [T]
USING (VALUES ('!Default_Materials_1', DEFAULT),
              ('00_NEW',30)) AS [S] ([NAME], [TYP])
ON [T].[NAME] = [S].[NAME]
WHEN MATCHED THEN
UPDATE SET [TYP]=[S].[TYP]
WHEN NOT MATCHED THEN
INSERT ([NAME], [TYP]) VALUES ([NAME], [TYP]);

In case anyone is interested here is the hugsql query:

-- :name insert-or-update-values! :!
-- :doc Insert the values into the database or if it matches the supplied where columns update the row
/* :require [wcc-variables.db.sql-macros :refer [columns values merge-set-  columns merge-on-columns]] */
MERGE INTO :i:table [T]
  USING ( VALUES --~ (values params options)
        ) AS [S] --~ (columns params options)
        ON --~ (merge-on-columns params options "T" "S")
WHEN MATCHED THEN
 UPDATE 
 SET --~ (merge-set-columns params options "S")
WHEN NOT MATCHED THEN
 INSERT --~ (columns params options)
 VALUES --~ (columns params options)

And the output of a simple query it creates in hugsql's queryvec format:

[MERGE INTO [dbo].[IMOS] [T]
  USING ( VALUES (?,?,?,?,?,?,DEFAULT,?,?,DEFAULT,DEFAULT,DEFAULT,?,DEFAULT,DEFAULT,?)
        ) AS [S] ([CATEGORY],[OPTINFO],[PRODUCER],[FAMILY],[WERT],[NAME],[POS],[ORDERID],[SOURCE],[CATALOG_ID],[DATE_LASTCHANGE],[LENGTH],[OPTNR],[SYS],[WORKPLAN_ID],[TYP])
        ON [T].[NAME] = [S].[NAME] AND [T].[ORDERID] = [S].[ORDERID] AND   [T].[TYP] = [S].[TYP] AND [T].[OPTNR] = [S].[OPTNR]
WHEN MATCHED THEN
 UPDATE 
 SET [CATEGORY]=[S].[CATEGORY] AND [OPTINFO]=[S].[OPTINFO] AND [PRODUCER]=[S].[PRODUCER] AND [FAMILY]=[S].[FAMILY] AND [WERT]=[S].[WERT] AND [POS]=[S].[POS] AND [SOURCE]=[S].[SOURCE] AND [CATALOG_ID]=[S].[CATALOG_ID] AND [DATE_LASTCHANGE]=[S].[DATE_LASTCHANGE] AND [LENGTH]=[S].[LENGTH] AND [SYS]=[S].[SYS] AND [WORKPLAN_ID]=[S].[WORKPLAN_ID]
WHEN NOT MATCHED THEN
 INSERT ([CATEGORY],[OPTINFO],[PRODUCER],[FAMILY],[WERT],[NAME],[POS],[ORDERID],[SOURCE],[CATALOG_ID],[DATE_LASTCHANGE],[LENGTH],[OPTNR],[SYS],[WORKPLAN_ID],[TYP])
 VALUES ([CATEGORY],[OPTINFO],[PRODUCER],[FAMILY],[WERT],[NAME],[POS],[ORDERID],[SOURCE],[CATALOG_ID],[DATE_LASTCHANGE],[LENGTH],[OPTNR],[SYS],[WORKPLAN_ID],[TYP]) Wardrobe Set - 1 Wardrobe Trim Stile Rail Zone Reveal JEFFWCC 1_WRDB_TM_FAM_STIR 0 1_WRDB_TM_STIR_ZR  IMOSADMIN 0 100]

Best Answer

According to MS-DOCS:

DEFAULT

Forces the Database Engine to insert the default value defined for a column. If a default does not exist for the column and the column allows null values, NULL is inserted. DEFAULT is not valid for an identity column. When specified in a table value constructor, DEFAULT is allowed only in an INSERT statement.

You cannot use DEFAULT in a MERGE statement. I suppose it is because it cannot evaluate DEFAULT value for a single TVC.

SELECT * FROM (VALUES(1, 'D')) AS T(A,B);
GO

 A | B 
-: | :-
 1 | D 

SELECT * FROM (VALUES(1, DEFAULT)) AS T(A,B);
GO

Msg 156 Level 15 State 1 Line 1
Incorrect syntax near the keyword 'DEFAULT'.

But, as far as you know DEFAULT values of your table, you could change the word DEFAULT by the actual default value:

MERGE INTO [dbo].[IMOS] [T]
USING 
    (VALUES ('!Default_Materials_1', 0), ('00_NEW',30)) AS [S] ([NAME], [TYP])
ON [T].[NAME] = [S].[NAME]
WHEN MATCHED THEN
UPDATE SET [TYP]=[S].[TYP]
WHEN NOT MATCHED THEN
INSERT ([NAME], [TYP]) VALUES ([NAME], [TYP]);

Example:

CREATE TABLE TBL
(
    ID INT IDENTITY PRIMARY KEY,
    FOO INT DEFAULT 0,
    BAR VARCHAR(10) DEFAULT 'X'   --< DEFAULT value for BAR
);
GO

INSERT INTO TBL VALUES (1, 'A'), (2, 'B'), (3, 'C');
GO

Now replace DEFAULT by 'X':

MERGE INTO TBL
USING (VALUES (1, 'X')) AS T(A,B)
ON T.A = TBL.FOO
WHEN MATCHED THEN 
UPDATE 
    SET BAR = B
WHEN NOT MATCHED THEN
INSERT (FOO, BAR) VALUES (T.A, T.B);

SELECT * FROM TBL;
GO

ID | FOO | BAR
-: | --: | :--
 1 |   1 | X  
 2 |   2 | B  
 3 |   3 | C

db<>fiddle here

In HugSQL:

MERGE INTO TBL
USING (VALUES (?, 'X')) AS T(A,B)
ON T.A = TBL.FOO
WHEN MATCHED THEN 
UPDATE 
    SET BAR = B
WHEN NOT MATCHED THEN
INSERT (FOO, BAR) VALUES (T.A, T.B);

Using a TYPE

On SQL-Server 2014 you could use a TYPE (alias data type).

CREATE TABLE dbo.MyTest 
(
    Id int primary key, 
    Val char(3) DEFAULT 'aaa'
);

INSERT INTO dbo.MyTest VALUES (1,'V1'),(2,'V2'),(10,'V10'),(11,'V11');

CREATE TYPE dbo.Mytype AS 
TABLE 
(
    Id  int PRIMARY KEY,
    Bar char(3) DEFAULT 'aaa'
);

Obviously table and type schema must be the same.

DECLARE @myData Mytype;

INSERT INTO @myData(Id, Bar) VALUES (1, DEFAULT),(3, 'V3')

MERGE INTO dbo.MyTest
USING (SELECT Id, Bar FROM @myData) src
ON MyTest.Id = src.Id
WHEN MATCHED THEN 
UPDATE SET Val = src.Bar
WHEN NOT MATCHED THEN
INSERT (Id, Val) VALUES (src.Id, src.Bar);

SELECT * FROM MyTest;
GO

Id | Val
-: | :--
 1 | aaa
 2 | V2 
 3 | V3 
10 | V10
11 | V11

db<>fiddle here

Related Question