Try after your code:
declare @loop int = 0
while @loop < 20
begin
set @loop = @loop + 1;
insert into #myTest2
(col1, col2test1)
select 'Test insert into #myTest2', * from
(insert into #myTest1 (col1) output inserted.col2Test1 values ('Test Insert into #myTest1 iteration: ' + cast(@loop as varchar(10)))) s
end
select * from #myTest1;
select * from #myTest2;
Also found this article which may be even simpler sample:
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...
If it's not broken, fix it until it is.
My Blog