SQL: Using an INSERT within a SELECT statement

Question

I have a SQL SELECT statement that will create its results in XML Format. I would like to INSERT into another table (Log via insert) every time a row is selected from the original SELECT Statement. Is there anyway to include an INSERT Statement inside of a SELECT Statement?

    SELECT      cs_ads_StoreLocations.LocationGUID, *Bunch of Selects AS Distance 
FROM         *Bunch of Inter Joins*

WHERE     *Bunch of conditions*
ORDER BY *You don't want to know*
FOR XML AUTO

INSERT INTO cs_ads_StoreLog (LocationGUID) VALUES (*DISTINCT cs_ads_StoreLocations.LocationGUID from select statement above*)

This is just some sample code that has the INSERT outside of the SELECT statement. I need something that has it inside the SELECT Statement or another way of running an INSERT

Just to clarify. This sample code is part of a Stored Proc

Answer 1

Perhaps dynamic SQL would solve this? Example here. Or maybe you could store the values in a table var, and do your insert on that.

Answer 2

INSERT will actually accept a SELECT as its input. You can just use this command (your SELECT copied as a subquery, though you could probably refactor it to return those distinct GUIDs without a subquery...but I'd need to see the whole monster).

insert into (cs_ads_StoreLog)
select distinct
    a.LocationGUID
from
    (SELECT
        cs_ads_StoreLocations.LocationGUID, *Bunch of Selects AS Distance 
    FROM
        *Bunch of Inter Joins*
    WHERE
        *Bunch of conditions*
    ORDER BY 
        *You don't want to know*
    ) a

Then, you can just return the SELECT that you have (it'll be cached, so you don't need to worry about performance hits too much).