INSERT INTO.. SELECT with default keyword

Question

Could not find anything related to this issue I am having, google only seems to return how to use the default keyword with INSERT INTO.. VALUES.

I have a table with the following columns:

RecordBookID	Shop ID	Created	Expires
uniqueidentifier	int	datetime	datetime

RecordBookID has a default constraint that uses newid() as the default

I can run the following with expected results:

insert into GoodsIn_Record_Books (RecordBookID, ShopID, Created, Expires)
values (default, 201, '2022-07-22 11:24:00.000', '2022-12-31 23:59:00.000')

RecordBookID	Shop ID	Created	Expires
EA5CB534-1DC3-46FA-B4F4-8EE70B974956	201	2022-07-22 11:24:00.000	2022-12-31 23:59:00.000

However I want to insert into with data from another table like so:

insert into GoodsIn_Record_Books (RecordBookID, ShopID, Created, Expires)
select default, ShopID, '2022-07-22 11:24:00.000', '2022-12-31 23:59:00.000' from System_Shops

Where the default keyword uses the default value of RecordBookID

System_Shops table:

ShopID	Name	...
201	WEST BAY	...
202	TUMMEL VALLEY	...
...	...	...

Hopefully this makes sense, first post on here so apologies if it's wrong in any way.

Cheers

Avallex · Answer 1 · 2022-07-22T12:16:32.243

1

"Have you tried omitting RecordBookID from the INSERT...SELECT entirely? I would expect that to use the default constraint value." - @Dan Guzman

insert into GoodsIn_Record_Books (ShopID, Created, Expires)
select ShopID, '2022-07-22 11:24:00.000', '2022-12-31 23:59:00.000'
from System_Shops

edited Jul 22 '22 at 12:16

answered Jul 22 '22 at 11:42

Avallex

7
3

score 0 · Answer 2 · answered Jul 22 '22 at 14:38

The default values will get added by default if you dont specify the column in the insert and values statement.

CREATE TABLE #tmp
(RecordBookID UNIQUEIDENTIFIER CONSTRAINT DF_tmp DEFAULT NEWID(),
 ShopID       INT,
 Created      DATETIME,
 Expires      DATETIME
);
INSERT INTO #tmp(ShopID, Created, Expires)
VALUES(201, '2022-07-22 11:24:00.000', '2022-12-31 23:59:00.000');
select * from #tmp;

To insert a row with only the default values populated, it can be done like so:

INSERT INTO #tmp
DEFAULT VALUES;

score 0 · Answer 3 · answered Jul 22 '22 at 17:07

Syntactically, what you are trying to do should be possible with MERGE:

MERGE INTO
  dbo.GoodsIn_Record_Books AS g
USING
  dbo.System_Shops AS s
ON
  1=0
WHEN NOT MATCHED THEN
  INSERT (RecordBookID, ShopID, Created, Expires)
  VALUES (DEFAULT, s.ShopID, '2022-07-22 11:24:00.000', '2022-12-31 23:59:00.000')
;

Please keep in mind two important things before using this, though:

You need the ability to stomach the syntax.
You need to be aware that MERGE has been reported to have numerous issues.

INSERT INTO.. SELECT with default keyword

3 Answers3