Store intersect result in table

Question

i'm having this query to get the doubles in a table, but on a different date:

SELECT  acf.ID , acf.ID2 , acf.CNK , acf.Omsch , acf.Groep , acf.prijs
FROM dbo.Acf_LstPrijzen acf
WHERE HstDate = '2013-12-01'
INTERSECT
SELECT  acf.ID , acf.ID2 , acf.CNK , acf.Omsch , acf.Groep , acf.Prijs
FROM dbo.Acf_LstPrijzen acf
WHERE HstDate = (SELECT MAX(acf2.hstDate) 
                         FROM dbo.Acf_LstPrijzen acf2
                         WHERE acf2.HstDate < '2013-12-01'
                         AND acf.id = acf2.id
                         GROUP BY id)

My question is: How can I insert this result in another table? Is it possible?

Duplicate of [this](http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from) and many others. — Zane, Jan 08 '14 at 14:14

score 3 · Accepted Answer · answered Jan 08 '14 at 13:54

Try this...

insert into #T

    select * from
    (
    SELECT  acf.ID , acf.ID2 , acf.CNK , acf.Omsch , acf.Groep , acf.prijs
    FROM dbo.Acf_LstPrijzen acf
    WHERE HstDate = '2013-12-01'
    INTERSECT
    SELECT  acf.ID , acf.ID2 , acf.CNK , acf.Omsch , acf.Groep , acf.Prijs
    FROM dbo.Acf_LstPrijzen acf
    WHERE HstDate = (SELECT MAX(acf2.hstDate) 
                             FROM dbo.Acf_LstPrijzen acf2
                             WHERE acf2.HstDate < '2013-12-01'
                             AND acf.id = acf2.id
                             GROUP BY id)
    )

Justin · Answer 2 · 2014-01-08T14:25:16.507

I would do something like this:

INSERT INTO TableName (column1,column2,column3,column4,column5,column6)
SELECT t.ID , t.ID2 , t.CNK , t.Omsch , t.Groep , t.prijs
FROM(
SELECT  t.ID , t.ID2 , t.CNK , t.Omsch , t.Groep , t.prijs
FROM dbo.t_LstPrijzen t
WHERE HstDate = '2013-12-01'
INTERSECT
SELECT  t1.ID , t1.ID2 , t1.CNK , t1.Omsch , t1.Groep , t1.Prijs,
FROM    (SELECT  ID, ID2, CNK , Omsch , Groep , Prijs,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY HstDate DESC) AS rnk
FROM dbo.t_LstPrijzen 
WHERE HstDate < '2013-12-01') t1
WHERE t1.rnk = 1 ) t

Store intersect result in table

2 Answers2