Andriy M (imported from SE)
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?
Top Answer
Andriy M (imported from SE)
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
  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.

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.