SQL: Creating a new table with query results

Question

I have calculated a number of new columns in my query and I was wondering if it is possible to save the query results into a new table/sheet. So when I open the new table I can see the query results without having to re-run the query every time upon opening SQL.

Here is the code I am using:

SELECT a.[CUSIP NUMBER],
        a.[CURRENT BALANCE],
       a.[ORIGINAL WA MATURITY],
       a.[CURRENT WA MATURITY],
       a.[PASS THRU RATE] [PASS THRU RATE],
       a.[CURRENT FACTOR],
       b.[CURRENT FACTOR],
       b.[ORIGINAL BALANCE],
        MonthlyRate,
        Payment,
        InterestPayment,
        Principle,
        ScheduledFace,
        PreviousFace,
        ScheduledFactor,
        SMM,
        CPR
   FROM   DBO.mbs032013 a
       JOIN dbo.mbs042013 b ON a.[CUSIP NUMBER] = b.[CUSIP NUMBER]     
       CROSS APPLY (Select (a.[PASS THRU RATE]*.01)/12) CA(MonthlyRate)
       CROSS APPLY (Select (a.[CURRENT BALANCE] * ((MonthlyRate)/((1-(1/power(1+    MonthlyRate, a.[CURRENT WA MATURITY]))))))) CA2(Payment) 
       Cross Apply (Select a.[CURRENT BALANCE] * MonthlyRate) CA3 (InterestPayment)
       Cross Apply (Select Payment - InterestPayment) CA4 (Principle) 
       Cross Apply (Select a.[ORIGINAL BALANCE] * a.[CURRENT FACTOR]) CA5 (PreviousFace)
       CROSS APPLY (Select PreviousFace - Principle) CA6(ScheduledFace)
       Cross Apply (Select ScheduledFace/a.[ORIGINAL BALANCE]) CA7 (ScheduledFactor)
       Cross Apply (Select 100 * (1-(b.[CURRENT FACTOR]/ScheduledFactor))) CA8(SMM)
       Cross Apply (Select (1-(power(1-SMM/100,12)))*100) CA9 (CPR)

      WHERE a.[CURRENT WA MATURITY] != 0 and a.[CURRENT BALANCE] != 0 and a.[CUSIP NUMBER] = '31416hag0'

The query ultimately generates a function called 'CPR' for bond analysis and I would like to add these results and the other columns to a permanent table.

I am using SQL Server 2012. Thanks!

Have you checked out SELECT ... INTO?

PinnyM
– PinnyM

2013-06-24 16:53:46 +00:00
Commented Jun 24, 2013 at 16:53 — PinnyM
– PinnyM, Commented Jun 24, 2013 at 16:53

PinnyM · Accepted Answer · 2013-06-24 16:55:56Z

3

SELECT a.[CUSIP NUMBER],
       ...
INTO newTable
FROM  DBO.mbs032013 a
...

answered Jun 24, 2013 at 16:55

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2501239 Over a year ago

This works but I can't find the new table that I have created. Where does it go?

PinnyM Over a year ago

@user2501239: It's called 'newTable'

PerfectPixel · Accepted Answer · 2013-06-24 16:56:14Z

2

Yes, the syntax is shown here: http://www.w3schools.com/sql/sql_select_into.asp

SELECT *
INTO newtable [IN externaldb]
FROM table1;

answered Jun 24, 2013 at 16:56

PerfectPixel

1,9881 gold badge17 silver badges18 bronze badges

Comments

cosmos · Accepted Answer · 2013-06-24 17:07:39Z

SELECT a.[CUSIP NUMBER],
    a.[CURRENT BALANCE],
   a.[ORIGINAL WA MATURITY],
   a.[CURRENT WA MATURITY],
   a.[PASS THRU RATE] [PASS THRU RATE],
   a.[CURRENT FACTOR],
   b.[CURRENT FACTOR],
   b.[ORIGINAL BALANCE],
    MonthlyRate,
    Payment,
    InterestPayment,
    Principle,
    ScheduledFace,
    PreviousFace,
    ScheduledFactor,
    SMM,
    CPR 
    INTO NewTable           --the new table to be created
  FROM   DBO.mbs032013 a
   JOIN dbo.mbs042013 b ON a.[CUSIP NUMBER] = b.[CUSIP NUMBER]     
   CROSS APPLY (Select (a.[PASS THRU RATE]*.01)/12) CA(MonthlyRate)
   CROSS APPLY (Select (a.[CURRENT BALANCE] * ((MonthlyRate)/((1-(1/power(1+    MonthlyRate, a.[CURRENT WA MATURITY]))))))) CA2(Payment) 
   Cross Apply (Select a.[CURRENT BALANCE] * MonthlyRate) CA3 (InterestPayment)
   Cross Apply (Select Payment - InterestPayment) CA4 (Principle) 
   Cross Apply (Select a.[ORIGINAL BALANCE] * a.[CURRENT FACTOR]) CA5 (PreviousFace)
   CROSS APPLY (Select PreviousFace - Principle) CA6(ScheduledFace)
   Cross Apply (Select ScheduledFace/a.[ORIGINAL BALANCE]) CA7 (ScheduledFactor)
   Cross Apply (Select 100 * (1-(b.[CURRENT FACTOR]/ScheduledFactor))) CA8(SMM)
   Cross Apply (Select (1-(power(1-SMM/100,12)))*100) CA9 (CPR)

  WHERE a.[CURRENT WA MATURITY] != 0 and a.[CURRENT BALANCE] != 0 and a.[CUSIP NUMBER] = '31416hag0'

David Scott · Accepted Answer · 2013-06-24 16:57:51Z

1

A table that is specifically a subset of another/set of others is best represented in a VIEW.

CREATE VIEW [dbo].[newView]
AS
SELECT a.[CUSIP NUMBER],
        a.[CURRENT BALANCE],
       a.[ORIGINAL WA MATURITY],
       a.[CURRENT WA MATURITY],
       a.[PASS THRU RATE] [PASS THRU RATE],
       a.[CURRENT FACTOR],
       b.[CURRENT FACTOR],
       b.[ORIGINAL BALANCE],
        MonthlyRate,
        Payment,
        InterestPayment,
        Principle,
        ScheduledFace,
        PreviousFace,
        ScheduledFactor,
        SMM,
        CPR
   FROM   DBO.mbs032013 a
       JOIN dbo.mbs042013 b ON a.[CUSIP NUMBER] = b.[CUSIP NUMBER]     
       CROSS APPLY (Select (a.[PASS THRU RATE]*.01)/12) CA(MonthlyRate)
       CROSS APPLY (Select (a.[CURRENT BALANCE] * ((MonthlyRate)/((1-(1/power(1+    MonthlyRate, a.[CURRENT WA MATURITY]))))))) CA2(Payment) 
       Cross Apply (Select a.[CURRENT BALANCE] * MonthlyRate) CA3 (InterestPayment)
       Cross Apply (Select Payment - InterestPayment) CA4 (Principle) 
       Cross Apply (Select a.[ORIGINAL BALANCE] * a.[CURRENT FACTOR]) CA5 (PreviousFace)
       CROSS APPLY (Select PreviousFace - Principle) CA6(ScheduledFace)
       Cross Apply (Select ScheduledFace/a.[ORIGINAL BALANCE]) CA7 (ScheduledFactor)
       Cross Apply (Select 100 * (1-(b.[CURRENT FACTOR]/ScheduledFactor))) CA8(SMM)
       Cross Apply (Select (1-(power(1-SMM/100,12)))*100) CA9 (CPR)

      WHERE a.[CURRENT WA MATURITY] != 0 and a.[CURRENT BALANCE] != 0 and a.[CUSIP NUMBER] = '31416hag0'

answered Jun 24, 2013 at 16:57

David Scott

1,1149 silver badges11 bronze badges

3 Comments

PinnyM Over a year ago

Except that a VIEW will rerun the query every time it is accessed. From the phrasing of the question, the OP apparently wants to freeze the data as it is without needing to regenerate it.

David Scott Over a year ago

Wouldn't INSERT INTO newTable SELECT * FROM ...... etc have to be used since this will be a historical table and SELECT INTO creates a newTable everytime?

user2501239 Over a year ago

Yes I just want to freeze the data

Collectives™ on Stack Overflow

SQL: Creating a new table with query results

4 Answers 4

2 Comments

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related