开发者

SQL Insert to Temp Table Without Specifying Values?

I have a stored procedure that currently uses one CTE. This one works like so:

WITH MY_CTE AS
(
    // Logic here uses SELECT * from a single table.
)
SELECT *
INTO #Tasks
FROM MY_CTE;

I now have a requirement to optionally call another CTE that will add more data to the original temp table. I was hoping to do something like this:

IF @Option = 1
    BEGIN
        INSERT INTO #Tasks
        (
            WITH MY_OTHER_CTE
            (
                // Logic here uses SELECT * from same table as first CTE.
            )
            SELECT *
            FROM MY_OTHER_CTE
        )
    END

The problem is that the INSERT INTO #Tasks call requires specifying VALUES columns. Both CTE's return records from the same table. The goo开发者_StackOverflow中文版d benefit of original stored procedure was that it would work even if columns in that table changed, since I was using SELECT *. I could specify the columns knowing what they are in the shared table, but I lose that benefit (which in this specific case is an extremely useful benefit). Is there a way to accomplish what I'm trying to do, knowing that they are both selecting from the same table and that the columns will always match?


Does this work?

;WITH MY_OTHER_CTE AS
(
    // Logic here uses SELECT * from same table as first CTE.
)
INSERT INTO #Tasks
    SELECT *
    FROM MY_OTHER_CTE

You might have another challenge if the table you're selecting from (and hence the temp table) has an identity column


I'll start with the obligatory "listing your columns out is good practice for 6,354 reasons." But we've all been there where you can't ;-)

Anyway, the way I would tackle this is with derived tables & Unions. The key here is doing a series of UNION's and using your WHERE criteria to drop out the queries you don't want to run. Obviously all tables need to have the same datatypes in the same order for this to work, but the code could look something like....

WITH MY_CTE AS (

    SELECT *
    FROM (
        SELECT  * 
        FROM    FirstTable
        WHERE   <your criteria here>

            UNION 

        SELECT  *
        FROM    FirstTable_OR_SomeOtherTable
        WHERE   <your criteria>
            AND @Option1 = 1

            UNION 

        SELECT *
        FROM    OnAndOnAndSoOn
        WHERE   <your criteria>
            AND @Option2 = 1

    ) AS MyDerivedTable   
)
SELECT *
INTO #Tasks
FROM MY_CTE;

EDIT

I guess I ignored your "two CTE's" request, but I suspect this will get you to the same place.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜