Is there a way to store two different results with a single traversal of the table

Question

I have two temp tables where results from a single table are stored with different filtering applied.

INSERT INTO TEMP1
SELECT * 
FROM   MYTABLE
WHERE  MYTABLE.ID = x;

INSERT INTO TEMP2
SELECT * 
FROM   MYTABLE
WHERE  MYTABLE.ID = x
AND    MYTABLE.NAME= y

Is there a way to do these two inserts with a single query?

Why would you even want to do something like this? Why not leave the data where it is and simply filter on retrieval? — eggyal, Apr 29 '13 at 17:20
Please tag correctly. Are you using SQL Server or MySQL? And what version? And why do you need to store these values multiple times? Have you considered indexing the base table so that you don't need to store multiple copies of the data to aid queries? — Aaron Bertrand, Apr 29 '13 at 17:21
Totally understand the question about the need to do this sort of operation. I am using the temp table for other recursive table operations and the temp tables offer more readability — Abhishek Iyer, Apr 29 '13 at 21:16

score 2 · Accepted Answer · answered Apr 29 '13 at 17:33

Like Aaron mentioned you tagged this SQL Server and MySQL which makes it difficult to give you a precise answer. If this is SQL Server this should work...

INSERT  INTO TEMP2
SELECT  *
FROM   (INSERT  INTO TEMP1
        OUTPUT  Inserted.*
        SELECT  *
        FROM    MYTABLE
        WHERE   MYTABLE.ID = x) n
WHERE   n.NAME = y

Is there a way to store two different results with a single traversal of the table

1 Answers1