Insert multiple rows into a table with only an IDENTITY column [duplicate]

Question

I have a table called dbo.Groups defined like this:

CREATE TABLE dbo.Groups
(
  GroupID int NOT NULL IDENTITY (1,1) PRIMARY KEY
);

The table really consists of just the one IDENTITY column.

Sometimes I want to insert into it multiple rows at once and get the generated IDs. (I already have a pre-defined table variable called @output with a single ID column to be used in the OUTPUT clause.)

Now I know how I would proceed if it was a single row:

INSERT INTO
  dbo.GroupID
OUTPUT
  inserted.GroupID INTO @output (ID)
DEFAULT VALUES
;

But I want to be able to insert two or more in one go. The actual number is determined by the number of rows returned by this query:

SELECT
  *
FROM
  dbo.MySource
;

So if the query returns one row, I want to insert one row into dbo.Groups and return the generated GroupID. If it is a hundred rows, then I would expect a hundred rows inserted and a hundred IDs generated and returned at once.

One obvious method is to insert one row at a time in a loop. I would like to avoid that and use a set-based approach instead, something along the lines of

INSERT INTO
  dbo.GroupID
OUTPUT
  inserted.GroupID INTO @output (ID)
SELECT
  ...  -- what?
FROM
  dbo.MySource
;

Is there a way to insert multiple rows into a table with just an IDENTITY column in (preferably) a single statement?

Answer 1

As of writing this, there is no way to insert multiple rows into just an IDENTITY column using an INSERT statement. The DEFAULT VALUES placeholder stands for just one row. And the INSERT ... SELECT syntax has no extension to support the same functionality as the DEFAULT VALUES clause.

Instead, you can use a MERGE statement to achieve the goal:

MERGE INTO
  dbo.Groups AS tgt
USING
  dbo.MySource AS src  -- << use your source row set here
ON
  1 = 0
WHEN NOT MATCHED THEN
  INSERT DEFAULT VALUES
OUTPUT
  inserted.GroupID INTO @output (ID)
;

The ON 1 = 0 clause basically turns the MERGE into a pure INSERT, because the explicitly false condition causes all the source rows not to be matched and thus trigger the WHEN NOT MATCHED THEN branch of the statement. Now, the WHEN NOT MATCHED THEN branch expects a single-row insert definition, where the familiar DEFAULT VALUES is perfectly valid. As the result, you effectively get an INSERT ... SELECT statement with the functionality of INSERT ... DEFAULT VALUES for an arbitrary number of rows.