Couldn’t return output with SRC Key in an INSERT statement

I want the OUTPUT clause to return both the source key and the target key like below :

INSERT INTO sales.stores(store_id,store_name) 
    OUTPUT deleted.store_id,deleted.store_name, inserted.store_id,inserted.store_name
    VALUES (4,'Adidas'),(5,'Nike')

I am having this following error :

The multi-part identifier "deleted.store_id" could not be bound.

The multi-part identifier "deleted.store_name" could not be bound.

Answer

To be clear, in INSERT, UPDATE, and DELETE statements, you can only refer to columns from the target table in the OUTPUT clause. In a MERGE statement you can refer to columns from both the target and the source.

This cannot be done directly in the INSERT statement because you don’t have access to the source table. So you need to use a MERGE statement here :

MERGE INTO sales.stores AS TGT
USING ( 

VALUES(4,'Adidas'),(5,'Nike')

) AS SRC (store_id,store_name)
ON 1 = 2
WHEN NOT MATCHED THEN
INSERT(store_id, store_name) VALUES(store_id,store_name)
OUTPUT
SRC.*, inserted.*;

The INSERT is only allowed when the MERGE condition is false.