没有指定列的SQL Server插入

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

我有一个具有自动生成ID列的表(仅此而已!)

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.

我想要插入到这个表中并提取生成的id。但是，如果可能的话，我不能算出来。它似乎在抱怨缺少值/列。

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.

但是我真的不想。我也可以指定我自己的ID并插入它，但同样，我也不想这么做。

2 个解决方案

#1

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

虽然我不确定你为什么需要这样一个表格，但你的问题的答案是使用关键字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.

另一种选择是使用默认值，如Pawan Kumar的答案所示。

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.

这两个选项的关键区别在于，指定列列表和使用关键字default可以提供更多的控制。当该表只有一个列时，它看起来并不多，但是如果您将向该表添加列，并希望向它们插入特定的值，那么使用默认值将不再是有效的选项。

From Microsoft Docs on INSERT (Transact-SQL):

来自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.

强制数据库引擎加载为列定义的默认值。如果列不存在默认值，列允许空值，则插入null。对于使用时间戳数据类型定义的列，插入下一个时间戳值。默认值对标识列无效。

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.

如您所见，默认值是基于列的，而默认值是基于行的。

#2

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

最后3个输出来自我的笔记本电脑

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

#1