A stored procedure is using below query to fetch and return results to client.
select
@Lid, *
from
CurrentProductSet cps
where
cps.State = @state
and cps.ProductName in (select gbb.ProductName
from HMCGoodBetterBest gbb
where gbb.HMC_Hospital = @hospital
and gbb.HMC_Extras = @extra);
Can you please guide me how I can store these results in anther table for further use before returning them to client. Just don't want to fetch data twice or use a table variable. I have created another table 'Temp_CurrentProductSet
'.
Edit:
I tried using into
clause trying below code but I get this error:
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Code:
select
@Lid, *
into
Temp_CurrentProductSet
from
CurrentProductSet cps
where
cps.State = @state
and cps.ProductName in (select gbb.ProductName
from HMCGoodBetterBest gbb
where gbb.HMC_Hospital = @hospital
and gbb.HMC_Extras = @extra);
**You need to use output clause**
insert into Temp_CurrentProductSet output Inserted.*
select
@Lid, *
from
CurrentProductSet cps
where
cps.State = @state
and cps.ProductName in (select gbb.ProductName
from HMCGoodBetterBest gbb
where gbb.HMC_Hospital = @hospital
and gbb.HMC_Extras = @extra);