Insert multiple rows into a table with only an IDENTITY column - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

Insert multiple rows into a table with only an IDENTITY column

sql-server sql-server-2008 add tag

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.

1 Answer