INSERT INTO with SELECT using OUTPUT

Question

I want to store the pt_id into my temporary table by using OUTPUT, however I'm not inserting pt_id into ct_tarf, what should I do?

I get the following error:

The multi-part identifier 'pt_id' could not be bound.

Query:

DECLARE @tbl_ids_tarf TABLE (pt_id INT, pt_id_tarf INT)

INSERT INTO dbo.ct_tarf(tr_id_serv, tr_name, tr_money)
OUTPUT pt_id, inserted.tr_id INTO @tbl_ids_tarf(ptr_id, ptr_id_tarf)
   SELECT 
      pt_id_serv, pt_name, pt_money
   FROM 
      dbo.opr_prop_tar
   WHERE 
      pt_id_tarf

Answer 1

SQL2008+:

Assuming that you want to insert into @tbl_ids_tarf (which is target of OUTPUT ... INTO clause) values from columns that are not returned by inserted or deleted virtual tables then one solution is to use MERGE statement instead of INSERT:

DECLARE @Target TABLE (
    Col1 INT
)
INSERT  @Target VALUES (1), (2);

DECLARE @Output TABLE (Col1 INT, ColB INT);

;WITH Source
AS (
    SELECT  * 
    FROM    (VALUES (10, 100), (20, 200), (30, 300)) x(ColA, ColB)
)
MERGE INTO @Target x
USING Source y ON x.Col1 = y.ColA
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (Col1) VALUES (y.ColA)
OUTPUT inserted.Col1, y.ColB INTO @Output (Col1, ColB); 
--                     ^-- y.ColB isn't returned by inserted or deleted virtual tables. 
-- inserted and deleted are based on `@Target` table [variable]

SELECT * FROM @Output;
/*
Col1        ColB
----------- -----------
10          100
20          200
30          300
*/