SQL Server : output inserted.Id join with inserted temporary user defined table
sql, t-sql
Solution
Use MERGE not INSERT.
This allows you to access the source tables in the OUTPUT clause for INSERTions.
Example: http://sqlblog.com/blogs/jamie_thomson/archive/2010/01/06/merge-and-output-the-swiss-army-knife-of-t-sql.aspx
Problem
I got a problem.. I use a stored procedure with user defined table (`@logs`). I insert it to other database table (`InOutLog`), with command `OUTPUT into`, where I get `inserted` id. The main problem is what I want to insert my user defined table to 2 database tables: - `InOutLog`, where I get inserted id and.. - Want to take inserted id and other defined table values (`l.Title+';'+l.Comment`) and insert it to table `MessageLog` But I can't access `l.Title+';'+l.Comment`. Also I can not find any simple solution to merge my user defined table and temporary table with inserted id values.. Here is the code: ``` insert into InOutLog(NFCId, UserID, DateEnter, DateLeave, ProjectId, Status, ServerDateEnter) output inserted.Id, inserted.DateLeave, l.Title+';'+l.Comment, inserted.UserId into MessageLog(TagLogId, MessageDate, Answer, UserId) select l.NFCTagId, l.UserId, l.ScanDate, l.StartDate, @projectID, 0, getdate() from @logs l ``` Any suggestions? What's the best practise in this case?