Creating new tables from queries

The following is kind of handy:


create table new_table_name as select * from old_Table_name;

for instance:

create table phedex_node_cms_name_map as
select phedex_node.id as node_id, cms_name.id as CMS_NAME_ID
from phedex_node
inner join cms_name
on phedex_node.site = (select site_id from site_cms_name_map where CMS_NAME_ID=cms_name.id)

or even more awesomely:

create table phedex_node_cms_name_map as
select NODE_ID, CMS_NAME_ID from(
select phedex_node.name as node, cms_name.name as cmsname, phedex_node_cms_name_map_tmp.cms_name_id, phedex_node_cms_name_map_tmp.node_id
, regexp_substr(cms_name.name,'_[A-Z]+$',1)
from phedex_node
join phedex_node_cms_name_map_tmp on phedex_node_cms_name_map_tmp.node_id = phedex_node.id
join CMS_NAME on CMS_NAME.id = phedex_node_cms_name_map_tmp.cms_name_id
where phedex_node_cms_name_map_tmp.node_id in (
select node_id from (
select count(node_id) as count, node_id from phedex_node_cms_name_map_tmp group by node_id
) where count > 0
)
and
regexp_like (phedex_node.name,regexp_substr(cms_name.name,'_[A-Z]+$',1))
order by CMSNAME)