It is a mystery to me why Microsoft decides to implement features in a seemingly half-baked way. I say seemingly, because many times it turns out that implementation challenges really are formidable and the limitation is well justified. In this particular case, object of my attention is our dear friend, the output clause. One of the many useful options for this construct is an ability to reference columns from tables that were used in the FROM clause – the from_table_name.
This feature comes very useful when dealing with certain database structures, like for example – many to many relationships.
Let’s say we want to insert a batch of new clients and assign them reps in one procedure. No problem – insert into CLIENTS, get the IDs via output clause and use that to populate the CLIENT_REP table. But what if I want to use some of the info in the customer rep table while creating a new client, and then make sure that I assign the right client back to the rep that I used his/her info? It should also be simple, right? Insert into client with a select that joins on the customer rep table, then output a client_id/rep_id pair and we’re in business. Not so fast. This is where from_table_name construct would have been really helpful, but unfortunately it can not be used in an INSERT statement. Meaning when we insert into the CLIENT table, customer rep fields are not available to the output clause even if this table is in the FROM clause. This is a really odd limitation, and seems un-necessary, but it is what it is for now.
Note, that however not all is lost, there is a workaround for this issue. it seems kind of weird at first, but it definitely helps.
