-- John Aromando -- SQL Temp Tables --

use ogcPubLev3

--temp tables are not live / snapshots only
select *
into
	#tmpCust 
from
	customers

--execute temp table
select* from #tmpcust
select custname, custnum, state from #tmpcust

--remove temp table
drop table #tmpcust

/*	
# = only local qry may access it / expires when local qry is closed
## = any qry may access it as long as creation qry is open / global expires when all users have closed out
*/

--create all tables, all fields combined temp locally
SELECT 
	c.*, 
	s.ordnum, sldate, qty, 
	t.*,
	r.lname, fname, commrate,
	qty*slprice as OrderTotal 
into #AllMainRecords 
FROM Customers as c 
		JOIN Sales as s ON s.custnum = c.custnum 
		JOIN Titles as t ON t.partnum = s.partnum 
		JOIN Slspers as r ON r.repid = c.repid
	
--execute temp table	
select * from #allmainrecords order by ordertotal desc

--drop temp table
drop table 	#allmainrecords