INSERT WITH AS SELECT QUERY INTO A TABLE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

INSERT WITH AS SELECT QUERY INTO A TABLE

Author

Topic

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2015-02-07 : 13:44:53

how to insert the results of the below querry into a table

WITH AllResults
AS
(
SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2
)
,OrderedResults
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY exp_date DESC) AS rn
FROM AllResults
)
SELECT *
FROM OrderedResults
WHERE rn = 1;

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-02-07 : 13:50:59

Insert your insert statement (no pun intended) just before the final select.

WITH AllResults
AS
(
SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2
)
,OrderedResults
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY exp_date DESC) AS rn
FROM AllResults
)
INSERT INTO YOURDESTINATIONTABLE 
SELECT *
FROM OrderedResults
WHERE rn = 1;

As a matter of style as well as for robustness, I usually list the columns explicitly rather than using SELECT *.

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2015-02-07 : 14:59:20

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources