How to Get the Inserted values to a new table

Most often we may be needed to use the inserted values of one table into another table for various reasons. I know we can use a trigger  to get the values inserted to another table but we may not be having the flexibility to add the desired values to the other tables.  SQL server has one way by which we could obtain the inserted values of a table.


The SQL server books online has one such example at the following location
https://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx#CaptureResults




USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO


So the trick here is to create a table variable and populate the data to the table variable using the Output Inserted.Columnname  during the insert statement.