Can a SELECT statement return data and INSERT in to another SQL table at the same time?

Question

The below queries the Customer table reading the same data twice;

To insert selected fields in to a temp table for later use, and
To output data to the application calling it

Is there any variation of SQL SELECT statement that will achieve both objectives at the same time? (i.e. without reading the data twice?)

declare @CustomerID int = 1234 declare @IDsFromCustomer1 table (CustomerID int, TerritoryID int) --IDs from Customer table insert into @IDsFromCustomer1(CustomerID, TerritoryID) select CustomerID, TerritoryID from [Customer] where [Customer].CustomerID = @CustomerID --Return Customer table select [Customer].* from [Customer] where [Customer].CustomerID = @CustomerID

I don't think output would work with insert since it's only inserting two column but needs to return all and insert can only ouput the inserted values - however it's a good suggestion as merge can accomplish this, I have added it as a suggestion in my answer below. — Stu
– Stu, Commented Aug 1, 2021 at 11:12

SteveC · Accepted Answer · 2021-08-01 12:42:06Z

You could use the OUTPUT clause of the INSERT statement

declare @CustomerID int = 1234 declare @IDsFromCustomer1 table (CustomerID int, TerritoryID int) insert into @IDsFromCustomer1(CustomerID, TerritoryID) output inserted.* select @CustomerID CustomerID, @CustomerID TerritoryID; select * from @IDsFromCustomer1;

This script returns 2 identical query outputs. First, one row is returned from the OUTPUT of the INSERT statement. Second, all rows are returned from @IDsFromCustomer1.

Stu · Accepted Answer · 2021-08-01 11:10:24Z

You have three options.

The first is to select all required columns into the @table variable - do you really need select *? You can then return the data directly from @IDsFromCustomer1.

You would need to experiment with the effectiveness depending on the table width and logical reads in each case. Using set statistics io on will show you the logical reads, you can also see this in the properties of the execution plan in SSMS.

Your second option which may help would be to not use a table variable but declare an actual #temp table.

You could then return your data by selecting from #IDsFromCustomer1 and joining back to your Customer table.

Using a temp table would have the benefit of SQL Server having statistics on the data and an optimal execution plan, plus you may benefit from a clustered index on the temp table also.

Testing to see which yields lower logical reads will (in most cases) highlight the best implementation.

A third option might work for you as mentioned by Dale in the comments, depending on your usage in when you want to have the temp table data available and when you want to output your results.

Using merge you can insert your data and also output data at the same time.

for example you could do the following

merge @IDsFromCustomer1 as t using ( select * from Customer where CustomerID = @CustomerID ) as s on 1=1 when not matched then insert (CustomerID, TerritoryID) values (s.CustomerID, s.TerritoryID) output s.*;

Collectives™ on Stack Overflow

Can a SELECT statement return data and INSERT in to another SQL table at the same time?

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related