Temporary tables in SQL and how they behave

declare-temp-table-example.sql

/* BEGIN FILE (transaction) */

--  SYMANTICS: 
--  In the context of SQL there is an idea of a table veriable and a temporary           
    table. A table variable has a table structure and is more maluable than a           
    temp table but it is less persistent than a temp table.


create table #OneRowOneLine (num int) -- declare the temp table

insert into #OneRowOneLine values(1) -- put stuff in

go -- exec batch

select * from #OneRowOneLine -- table variable is still in memory

declare @testVar int = (select * from #OneRowOneLine) -- now set to a primitive variable

/* 
NOTE:

The above will not work if a table has more than one row or one column
    It will result in a scalar variable error. 
 */

print @testVar -- print the variable to the query console

GO

print @testVar -- this will produce an error, because after batch us executed primitive variables are removed from memory


print (select * from #OneRowOneLine); -- this will run, temp tables more persistent and last the an entire execution block that include batches.


/* END OF FILE (transaction) */

 -- temporary table is not destroyed.