Use inserted identity field value to link to identity field from source table question.

Question

Use inserted identity field value to link to identity field from source table question.

Leo-209864

Valued Member

Points: 57
More actions
January 16, 2018 at 9:13 am

#342375

Hi all
I have a final destination table with identity field that I need to get back and link it to temp table's identity field.
Example
table tmpA ( cust_id identity int, fname varchar(10) )
Cust_id   fname
1             bla
2             bla
3             me
table fnl ( cust_id identity int, fname varchar(10) )
Need to insert records from tmpA into fnl table and I need to build an intermidiate table with tmp_link(tmpA.cust_id, fnl.cust_id)
Is this possible? I was trying to use OUTPUT clause, but can't seem to make it work.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1

I'm not clear what you're trying to do. Can you restate the question? Please post according to forum netiquette. If you do that you will likely get faster and more responses.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 2

It looks like you have to use merge instead of insert if you want values from the old table in the output clause. So something like this.

MERGE #TEMP_TWO AS TARGET USING (SELECT * FROM #TEMP_ONE WHERE COL_ONE IN (1, 2)) AS SOURCE ON TARGET.COL_ONE IS NULL WHEN NOT MATCHED THEN INSERT (COL_TWO) VALUES(SOURCE.COL_TWO) OUTPUT SOURCE.COL_ONE, inserted.COL_ONE INTO #TEMP_LINK(OLD_ID, NEW_ID) ;

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

You can certainly do this with OUTPUT or MERGE.... the trick is that you need something in your insert to be common with the original table you're inserting from and it needs to be unique or there's really no chance of doing the match that you're trying for.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)