Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
OUTPUT in Insert
Message
 
General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
Miscellaneous
Thread ID:
01673377
Message ID:
01673398
Views:
43
Likes (1)
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
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform