1

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]
3
  • I don't think you can use DEFAULT there, that's a limitation in the implementation of MERGE. Commented May 10, 2018 at 12:30
  • Could you use a default value on the column instead?
    – Jacob H
    Commented May 10, 2018 at 12:44
  • Jacob H, I'm not sure I understand what you mean by use a default value on the column instead? All the columns have default constraint, I'm just not sure how to use the default in my query. As far as I understand the default is inserted when I don't supply a column for the insert or if I set the column to default using the DEFAULT keyword. I can't not supply a column if in the other rows I'm inserting have a non-default value and MERGE apparently can't take the DEFAULT keyword.
    – jeff
    Commented May 10, 2018 at 13:03

1 Answer 1

2

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

1
  • Thanks for the help. Obviously that works, however I was trying to create something more general. Right now I have a similar function using IF EXISTS, but it only works on one row at a time. My goal was to simplify my clojure code and maybe gain a speedup by not having thousands of individual SQL transactions. Right now I use the same function on ~30 different tables with >1k different columns. That is a bit too many to type in all the defaults by hand, not to mention there is a new version of the software I'm interfacing with coming out and I don't know how the db schema will change.
    – jeff
    Commented May 10, 2018 at 13:56

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.