After thinking about this, I decided to answer instead of comment to provide more detailed information.
Yes, an INSERT statement can use an OUTPUT clause. It can be specified before the VALUES clause. See the SQL Server Books Online (http://technet.microsoft.com/en-us/library/ms174335.aspx) for the authoritative T-SQL reference
I suggest you avoid MERGE
for unconditional inserts in production code. It provides no additional functionality, requires less codes, and is easier to maintain. Comparing the execution plans of a MERGE
versus INSERT
, the `MERGE plan incudes additional scalar operators for the extra constants. I wouldn't expect a significant execution time difference but compilation time might be a slightly higher.
Below are functionally identical examples of these two techniques.
DECLARE @value1 varchar(10)
, @value2 int;
DECLARE @test table ( col1 char(8000), col2 int );
MERGE test
USING
( SELECT 1 x
) AS [Source]
ON ( 1 = 0 ) -- Effectively Makes this an Insert as 1 Never Matches 0
WHEN NOT MATCHED THEN
INSERT ( [col1], [col2] )
VALUES ( @value1, @value2 )
OUTPUT
Inserted.[col1]
, Inserted.[col2]
INTO @test;
GO
DECLARE @value1 varchar(10)
, @value2 int;
DECLARE @test table ( col1 char(8000), col2 int );
INSERT INTO dbo.test
( [col1], [col2] )
OUTPUT Inserted.[col1], Inserted.[col2]
INTO @test
VALUES ( @value1, @value2 );
GO

MERGE
: mssqltips.com/sqlservertip/3074/… – Aaron Bertrand♦ May 8 '15 at 13:46