Inserting fetched records into new table

Posted on

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.

Leave a Reply

Your email address will not be published. Required fields are marked *