Stored procedure, storing results in another table before returning to client

Question

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);

Answer 1

**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);

Answer 2

As error suggests, you need to define alias name for each column name.

Try this instead,

insert into Temp_CurrentProductSet
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);

Answer 3

The key to your problem is in the error:

An object or column name is missing or empty.

You need to define a column name for your @Lid field, something like:

select @Lid as Lid, * 
    into Temp_CurrentProductSet
    from ...

Do realize, using SELECT INTO will create a new table. If you are trying to insert the values into an existing table, you need to use INSERT INTO SELECT.