create a table based on the results (output) of previous queries

Question

how can I create a table based on the output of previous queries? I tried Create table as select distinct syntax but it doesn't work. ps: the previous queries contain inner join SET dateformat dmy

select distinct c.peril, c.period, c.eventid, c.eventdate, c.loss_index_2016,
                c.loss_index_20182019, a.eventdatetime, b.region, a.year,a.eventid
from RetroNCTJan2019v5 a
inner join retropltheader2019v5 b
    on a.segmentid=b.segmentid
inner join Index2019 c
    on b.region = c.peril and a.Year = c.period and
       a.eventid=convert(numeric(30,0),convert(float(28),c.eventid)) and
       month(eventdate) = month(eventdatetime) and day(eventdate)=day(eventdatetime)

Does this answer your question? [How to create a table from select query result in SQL Server 2008](https://stackoverflow.com/questions/16683758/how-to-create-a-table-from-select-query-result-in-sql-server-2008) — astentx, Apr 16 '21 at 11:13
`DISTINCT` in a joined query usually indicates poorly though-out joins, which should possibly be `EXISTS` or `IN` — Charlieface, Apr 16 '21 at 12:26

Gordon Linoff · Answer 1 · 2021-04-16T11:05:13.540

In SQL Server you insert the results of a SELECT into a new table using INTO:

select distinct c.peril, c.period, c.eventid,c.eventdate, c.loss_index_2016, c.loss_index_20182019,
      a.eventdatetime, b.region, a.year,a.eventid
into new_table
from RetroNCTJan2019v5 a join
     retropltheader2019v5 b
     on a.segmentid = b.segmentid join
     Index2019 c
     on b.region = c.peril and a.Year = c.period and 
        a.eventid = convert(numeric(30,0), convert(float(28),c.eventid)) and
        month(eventdate) = month(eventdatetime) and
        day(eventdate) = day(eventdatetime)

Most databases use create table as, which in my opinion, is a little clearer.

That said, you should learn some better coding habits:

Use table aliases that are meaningful. This usually means abbreviations of the table names. Don't use arbitrary letters.
Qualify all column references, especially when your query has more than one table reference.
Converting to a float and then a numeric is probably unnecessary. In fact, having to do a type conversion in a JOIN condition is suspicious -- and even more so when the two columns have the same name. Same name --> same type.

create a table based on the results (output) of previous queries

1 Answers1