DML With Results

Immediately access just-modified data for further processing

Rating:

(0)

Itzik Ben-Gan

SQL Server Magazine

InstantDoc ID #46844

Have you ever needed to modify data and then—in the same transaction—access that just-modified data for further processing? For example, suppose you need to load multiple rows into an Orders table with one INSERT SELECT statement. An identity property automatically generates the order IDs. You need to access the just-loaded rows to get the newly generated order IDs, then use those IDs to generate order details. In SQL Server 2000, such a task is far from simple. SQL Server 2005, howe...

...This article is for paid Professional Members only.

Already a Professional Member? Please log in now:

Professional Membership

Monthly

Annual

VIP Membership

Monthly

Annual

Compare Member Benefits

Add a Comment

I read in BOL that you can also access columns from other tables in the FROM clause within the OUTPUT clause. This is very useful, but only applies to UPDATE and DELETE. I would really like the ability to access columns from other tables when I write an INSERT...SELECT... statement. Apparently, that is limited to the Inserted table only. Sometimes in migration scripts, the best way to map newly created ids is to use a column that does not exist in the table to which I am inserting, but will be in a child table. My code could be abbreviated even more if this capability was there.

johntkeller 1/16/2007 11:31:25 AM

Can this be accomplished in SQL 2000? This article mentions that this is a difficult task, but does not mention specifics.

lshores 6/9/2006 8:03:00 AM

You must log on before posting a comment.

Are you a new visitor? Register Here