Is there any way to retrieve inserted rows of a command

We probably all know SCOPE_IDENTITY() to retrieve the identity generated by a single insert. Currently I'm in the need of some kind of magic variable or function to retrieve all the rows generated by a statement, eg:

INSERT INTO [dbo].[myMagicTable]
(
    [name]
)
SELECT    [name]
FROM      [dbo].[myMagicSource]
WHERE     /* some weird where-clauses with several subselects ... */;

INSERT INTO [dbo].[myMagicBackupTable]
(
    [id],
    [name]
)
SELECT
    [id],
    [name]
FROM    ???

An insert trigger is no option, as this will perform a single insert which is a problem fo开发者_StackOverflow中文版r a batch of 10.000 rows... So, is there any way to achieve this? We are using mssql2005<

For SQL Server 2005+, you can use the OUTPUT clause.

DECLARE @InsertedIDs table(ID int);

INSERT INTO [dbo].[myMagicTable]
    OUTPUT INSERTED.ID
        INTO @InsertedIDs 
    SELECT ...

You could define a temporary table (possibly a table variable) and make use of the OUTPUT clause on your INSERT (you can make use of the Inserted pseudo-table, like in a trigger):

DECLARE @NewIDs TABLE (MagicID INT, Name VARCHAR(50))

INSERT INTO [dbo].[myMagicTable]([name])
OUTPUT Inserted.MagicID, Inserted.Name INTO @NewIDs(MagicID, Name)
  SELECT    [name]
  FROM      [dbo].[myMagicSource]
  WHERE     /

and then use that table variable after the INSERT:

INSERT INTO 
  [dbo].[myMagicBackupTable]([id], [name])
  SELECT MagicID, [name]
  FROM @NewIDs

and go from there.