Question :
I’m fetching some records with below query:
;with tb1 as (select * from tb where condition), select ... from tb1 join tb2 condition
I want to insert the records fetched with the above query to a new table. How can I do that?
I tried this:
select * into NewTable from (followed by above query)
and this:
;with Tb1 as (select * from tb where condition), select * into NewTable from (select ... from Tb1 join tb2 condition)
But it doesn’t work. Any tips? Thanks.
EDIT:
Acutal QUERY which I need to insert into New Table:
; with dub as
(select docnumber, sum(amount) summedamount from stg_invoice_cust_vw where year(crmcreateddatetime)=2017
group by docnumber
having count(docnumber)>1)
select dub.docnumber, dub.summedamount, sup.slamount SLAmount, (dub.summedamount - sup.slamount) diff from STG_INVOICE_SUP_VW sup inner join dub on dub.docnumber = sup.docnumber
where (dub.summedamount - sup.slamount) <> 0
Answer :
Instead of a SELECT
you just follow the CTE with a SELECT INTO
:
;WITH cte AS
(
SELECT x = CONVERT(int, 1)
)
SELECT x INTO dbo.TrashMe FROM cte;
With your new query:
; with dub as
(
select docnumber, sum(amount) summedamount
from stg_invoice_cust_vw where year(crmcreateddatetime)=2017
group by docnumber
having count(docnumber)>1
)
select
dub.docnumber,
dub.summedamount,
sup.slamount SLAmount,
(dub.summedamount - sup.slamount) diff
-----------
INTO dbo.NEW_TABLE_NAME --<--------------------------- *** HERE ***
-----------
from STG_INVOICE_SUP_VW sup
inner join dub on dub.docnumber = sup.docnumber
where (dub.summedamount - sup.slamount) <> 0
Note you can only do this once… the next time you run this code, you will get an error that NEW_TABLE_NAME
already exists.