Why did the author of this article had to "confuse me" :) with the Merge Statement?
Can't they stay on one topic? :)
>
>
https://www.codeproject.com/Articles/38871/Composable-DML-and-Merge-Statement-in-SQL-Server-2>
>>Here is my "real" example on which I would like to learn this Composable SQL:
>>
>>
>>if object_id('tempdb..#myTest1', N'U') is not null drop table #myTest1;
>>if object_id('tempdb..#myTest2', N'U') is not null drop table #myTest2;
>>
>>declare @InsertOutput table
>>(
>> new_ident int
>>)
>>create table #myTest1
>>(
>> col1 varchar(50),
>> col2Test1 int identity(1,1) not null
>>)
>>create table #myTest2
>>(
>> col1 varchar(50),
>> col2Test1 int
>>)
>>
>>
>>I would like to create a Insert into table #myTest1 and in the table #myTest2 in one statement, where the column col2Test1 of #myTest2 gets the value of the col2Test1 (Identity) of table #myTest1. (The column col2 of #myTest2 can get any dummy value, not important here).
>>
>>
>>Could you, please, show me how to do it?
>>
>>
>>>> Because, IMHO, the speed you gain with the Composable SQL vs two Inserts and output table is probably negligible (if anything).
>>>
>>>Speed is nothing compared to elegance :)
>>>
>>>Just think about a sub-query, you can say i.e.
>>>
>>>
select myData.* from (select * from myTable where myCondition) myData
>>>
>>>This was a terrible example of sub-query, completely unnecessary etc etc, but, it gives you the idea. When you put OUTPUT in your INSERT or UPDATE commands, is like it has an implicit sub-query and that is what you use. I am sure someone can explain it way better...
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham