About this 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: * 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?
