Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





Sybase » Transact-SQL » Coding » Temp Tables

Using Temporary Tables

     




Temp tables allow developers to create and scan tables within a stored procedure - and
have the tables totally isolated from all other database connections. This is very
valuable when results need to be processed several times within a loop, or when
a complex result set is expected (like a crosstab). Note that temp table transactions
are logged within tempdb (exception: select into create statements).



create proc proc_gen_report (@region_id integer) as

declare @total money


/* standard create */

create table #rpt (
store_id integer not null,
store_cd char(5) not null,
inv_count integer not null,
total_sales money not null
)


/* create using select into - make sure 'select into' is turned on */

select t1.cus_id, t1.cus_name, sum(t2.inv_amount) 'inv_summary'
into #cus_invoices
from customer t1, invoice t2
where t2.cus_id = t1.cus_id


/* Processing occurs, using temp table(s) where needed. */
/* Temp tables can be used in joins, aggregates, updates, etc. */


drop table #rpt
drop table #cus_invoices

return
go












Sybase : Related Topics

Sybase : Transact-SQL : Transact SQL: numeric functions
Sybase : Transact-SQL : Transact SQL: string functions
Sybase : Transact-SQL : Transact SQL: date/time functions
Sybase : Transact-SQL : Transact SQL: misc functions
Sybase : Transact-SQL : Transact SQL: Conditionals
Sybase : Transact-SQL : Transact SQL: looping constructs
Sybase : Transact-SQL : Transact SQL: Cursors
Sybase : Transact-SQL : Transact SQL: Complex Updates
Sybase : Transact-SQL : Transact SQL: Finding duplicate rows in a table
Sybase : Transact-SQL : Inner/Outer Joins
Sybase : Transact-SQL : Isolation Levels
Sybase : Transact-SQL : Reporting: SQL Performance and Tuning
Sybase : Transact-SQL : Case Statement
Sybase : Transact-SQL : Date processing: stripping time from datetime
Sybase : Transact-SQL : Safely delete a large number of rows without blowing the t-log
Sybase : Transact-SQL : Transact SQL: date/time formats
Sybase : Transact-SQL : Creating a stored procedure
Sybase : Transact-SQL : Custom query plans
Sybase : Transact-SQL : Rowcount setting - limiting result sets
Sybase : Transact-SQL : Ranking Data - with Duplicates
Sybase : Transact-SQL : Forcing an index in a query
Sybase : Transact-SQL : Median Calculation, with pure SQL
Sybase : Transact-SQL : Lead and Lag with pure SQL

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.