SQL Server Insert with no specified columns

Question

I have a table with an auto-generated ID column (and that's all!)

CREATE TABLE [dbo].[EmailGroup](
    [EmailGroupGuid] [uniqueidentifier] NOT NULL 
    CONSTRAINT [PK_EmailGroup] PRIMARY KEY CLUSTERED ([EmailGroupGuid] ASC)
) ON [PRIMARY]


ALTER TABLE [dbo].[EmailGroup] 
    ADD  CONSTRAINT [DF_EmailGroup_EmailGroupGuid]  DEFAULT (newsequentialid()) FOR [EmailGroupGuid]

I want to INSERT into this table and extract the generated ID. but, I can't work out if it's possible. It seems to complain about the lack of values/columns.

DECLARE @Id TABLE (Id UNIQUEIDENTIFIER)

INSERT INTO EmailGroup
OUTPUT inserted.EmailGroupID INTO @Id

Is there any way to do this? I mean I could add a dummy column to the table and easily do this:

INSERT INTO EmailGroup (Dummy)
OUTPUT inserted.EmailGroupID INTO @Id
VALUES (1)

however I don't really want to.
I could also specify my own ID and insert that, but again, I don't really want to.

Why not just use an auto increment column?

Tim Biegeleisen
– Tim Biegeleisen

2018-05-08 04:55:22 +00:00
Commented May 8, 2018 at 4:55 — Tim Biegeleisen
– Tim Biegeleisen, Commented May 8, 2018 at 4:55

Zohar Peled · Accepted Answer · 2018-05-08 05:17:58Z

5

Though I'm not sure why would you need such a table, the answer to your question is to use the keyword DEFAULT:

INSERT INTO EmailGroup (EmailGroupGuid)
OUTPUT inserted.EmailGroupGuid INTO @Id
VALUES(DEFAULT);

Another option is to use DEFAULT VALUES, as shown in Pawan Kumar's answer.

The key difference between these two options is that specifying the columns list and using the keyword default gives you more control.
It doesn't seem much when the table have a single column, but if you will add columns to the table, and want to insert specific values to them, using default values will no longer be a valid option.

From Microsoft Docs on INSERT (Transact-SQL):

DEFAULT

Forces the Database Engine to load 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.
For a column defined with the timestamp data type, the next timestamp value is inserted.
DEFAULT is not valid for an identity column.

DEFAULT VALUES

Forces the new row to contain the default values defined for each column.

So as you can see, default is column based, while default values is row based.

edited May 8, 2018 at 5:17

answered May 8, 2018 at 5:06

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mr. TA Over a year ago

Is this possible with a SELECT as the source of inserted values?

Zohar Peled Over a year ago

@mr.ta I'm not sure because I've never tried it. My guess would be no, but I might be wrong and it's an easy thing to check. (Not so easy when writing on the cellphone, though ...)

Mr. TA Over a year ago

Couldn't find anything in the docs nor online. I guess the answer is no.

Pawan Kumar · Accepted Answer · 2018-05-08 05:06:32Z

3

Please use this.

CREATE TABLE [dbo].[EmailGroup]
(
    [EmailGroupGuid] [uniqueidentifier] NOT NULL CONSTRAINT [PK_EmailGroup] PRIMARY KEY CLUSTERED ([EmailGroupGuid] ASC)
) ON [PRIMARY]


ALTER TABLE [dbo].[EmailGroup] 
    ADD  CONSTRAINT [DF_EmailGroup_EmailGroupGuid]  DEFAULT (newsequentialid()) FOR [EmailGroupGuid]

DECLARE @Id TABLE (Id UNIQUEIDENTIFIER)

INSERT INTO EmailGroup 
OUTPUT inserted.EmailGroupGuid INTO @Id DEFAULT VALUES

SELECT * FROM @Id

last 3 OUTPUTs from my Laptop

--92832040-7D52-E811-B049-68F728AE8695
--2B6ADC5F-7D52-E811-B049-68F728AE8695
--0140AF66-7D52-E811-B049-68F728AE8695

answered May 8, 2018 at 5:06

Pawan Kumar

2,02112 silver badges12 bronze badges

Collectives™ on Stack Overflow

SQL Server Insert with no specified columns

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related