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?