CODEWITHSUNDEEP
sqlsql-servert-sql
Bob Lozano
Bob Lozano

Reputation: 592

INSERT INTO with SELECT using OUTPUT

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

Upvotes: 22

Views: 42911

Answers (2)

marc_s
marc_s

Reputation: 754230

You have several issues with your query - column naming is one, an incomplete WHERE clause is another, and a missing Inserted. prefix is the third.

Try this:

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 inserted.pt_id, inserted.tr_id INTO @tbl_ids_tarf(pt_id, pt_id_tarf)
--     *********                                         ******************
   SELECT 
      pt_id_serv, pt_name, pt_money
   FROM 
      dbo.opr_prop_tar
   WHERE 
      pt_id_tarf --........

Upvotes: 17

Bogdan Sahlean
Bogdan Sahlean

Reputation: 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
*/

Upvotes: 21

Related Questions