SQL:在SELECT语句中使用INSERT [英] SQL: Using an INSERT within a SELECT statement

问题描述

我有一条SQL SELECT语句，它将以XML格式创建其结果.每当从原始SELECT语句中选择一行时，我都想插入另一个表(通过插入记录).无论如何，在SELECT语句中是否包含INSERT语句?

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

这只是在SELECT语句之外具有INSERT的一些示例代码.我需要在SELECT语句中包含它的东西或运行INSERT的另一种方法

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

推荐答案

INSERT实际上将接受SELECT作为其输入.您可以只使用此命令(您将SELECT复制为子查询，尽管您可能可以重构它以返回那些没有子查询的不同GUID ...但是我需要查看整个怪物).

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

然后，您只需返回您拥有的SELECT(它将被缓存，因此您不必担心性能过高).

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

这篇关于SQL:在SELECT语句中使用INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！