CODEWITHSUNDEEP
sqlsql-servert-sqlsql-insert
AaronHolland
AaronHolland

Reputation: 1675

SQL Server Insert with no specified columns

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.

Upvotes: 4

Views: 1307

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

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.

Upvotes: 5

Pawan Kumar
Pawan Kumar

Reputation: 2011

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

Upvotes: 3

Related Questions