Re: Stored Procedures - Temporary Tables
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 04/07/04
- Next message: Louis Davidson: "Re: update more than 1 col with SELECT"
- Previous message: Anith Sen: "Re: Using the "ConnectionID" Property"
- In reply to: Stephen Cairns: "Stored Procedures - Temporary Tables"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 7 Apr 2004 12:22:33 -0500
Basically where you say DELETE dbo.S_type, change this to CREATE TABLE
#S_TYPE ( with ddl)
and change all references to S_TYPE to use the #tables. Then the temporary
storage for the proc will be in the tempdb, rather than the primary db. As
a quick change, you could include deletes at the end of your procedure as
well, and then the space you are using in the primary database will be
reclaimed after running the procedure.
I should also not that you could (and I won't use the word easily, because
you have a lot of stuff going on) most likely write your entire procedure in
a single statement with no explicit temporary storage whatsoever. It would
be pretty messy, but it could be done.
-- ---------------------------------------------------------------------------- Louis Davidson (drsql@hotmail.com) Compass Technology Management Pro SQL Server 2000 Database Design http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies will be ignored :) "Stephen Cairns" <S.Cairns@belfasttelegraph.co.uk> wrote in message news:7CA8F95F-EE7B-4A7C-9C74-AEA551503140@microsoft.com... > At present I have been designing a lot of stored procedures which are inserting data into a number of tables. However by doing this i am taking up a lot of space on the server and have been told to design temporary tables instead. My stored procedure is below can anyone tell me how I can create temporary tables to run my procedure as oppose to inserting data into tables. Im quite new to SQL so I'd really appreciate it if anyone could help me > > CREATE PROCEDURE dbo.ByWeekEnding > ( @t_week_end_date smalldatetime > ) > > AS > SET NOCOUNT ON > DELETE dbo.S_Type > DELETE dbo.R_Type > DELETE dbo.WEEKENDING_TABLE > DELETE REPORT_WKENDING > > INSERT INTO S_Type(t_row_type, publication_name, Expr1, t_week_end_date, calculated_price, t_quantity, t_allowance, account_no, day_of_week, timestamp, edition_name) > SELECT dbo.cms_out.t_row_type, dbo.cms_out.publication_name, (dbo.cms_out.t_quantity)*(dbo.cms_out.calculated_price) AS Expr1, dbo.cms_out.t_week_end_date, dbo.cms_out.calculated_price, dbo.cms_out.t_quantity, dbo.cms_out.t_allowance, dbo.cms_out.account_no, dbo.cms_out.day_of_week, dbo.cms_out.timestamp, dbo.cms_out.edition_name > FROM dbo.cms_out > WHERE dbo.cms_out.t_row_type= 'S' > AND dbo.cms_out.t_week_end_date = @t_week_end_date > AND dbo.cms_out.t_quantity >0 > > INSERT INTO R_Type(t_row_type, publication_name, Expr1, t_week_end_date, calculated_price, t_quantity, t_allowance, account_no, day_of_week, timestamp, edition_name) > SELECT dbo.cms_out.t_row_type, dbo.cms_out.publication_name, (dbo.cms_out.t_quantity)*(dbo.cms_out.calculated_price) AS Expr1, dbo.cms_out.t_week_end_date, dbo.cms_out.calculated_price, dbo.cms_out.t_quantity, dbo.cms_out.t_allowance, dbo.cms_out.account_no, dbo.cms_out.day_of_week, dbo.cms_out.timestamp, dbo.cms_out.edition_name > FROM dbo.cms_out > WHERE dbo.cms_out.t_row_type= 'R' > AND dbo.cms_out.t_week_end_date = @t_week_end_date > AND dbo.cms_out.t_quantity >0 > > INSERT INTO WEEKENDING_TABLE (publication_name, account_no, calculated_price, t_quantity, t_allowance, t_week_end_date, day_of_week, edition_name, S_Type_timestamp, R_Type_timestamp) > SELECT dbo.S_Type.publication_name, dbo.S_Type.account_no, dbo.S_Type.calculated_price, dbo.S_Type.t_quantity, dbo.R_Type.t_allowance, dbo.S_Type.t_week_end_date, dbo.S_Type.day_of_week, dbo.S_Type.edition_name, dbo.S_Type.timestamp, dbo.R_Type.timestamp > FROM dbo.S_Type LEFT JOIN dbo.R_Type ON (dbo.S_Type.edition_name = dbo.R_Type.edition_name) AND (dbo.S_Type.publication_name = dbo.R_Type.publication_name) AND (dbo.S_Type.day_of_week = dbo.R_Type.day_of_week) AND (dbo.S_Type.account_no = dbo.R_Type.account_no) AND (dbo.S_Type.calculated_price = dbo.R_Type.calculated_price); > > UPDATE dbo.WEEKENDING_TABLE SET dbo.WEEKENDING_TABLE.t_allowance = 0 > WHERE dbo.WEEKENDING_TABLE.t_allowance is null; > > INSERT INTO REPORT_WKENDING (publication_name, calculated_price, t_quantity, t_allowance, Copies, t_week_end_date, day_of_week, Rev, PubNumber, account_no, S_Type_timestamp, R_Type_timestamp, edition_name) > SELECT dbo.WEEKENDING_TABLE.publication_name, dbo.WEEKENDING_TABLE.calculated_price, dbo.WEEKENDING_TABLE.t_quantity, dbo.WEEKENDING_TABLE.t_allowance, (dbo.WEEKENDING_TABLE.t_quantity)-(dbo.WEEKENDING_TABLE.t_allowance) AS Copies, dbo.WEEKENDING_TABLE.t_week_end_date, dbo.WEEKENDING_TABLE.day_of_week, ((dbo.WEEKENDING_TABLE.t_quantity)-(dbo.WEEKENDING_TABLE.t_allowance))*(dbo. WEEKENDING_TABLE.calculated_price) AS Rev, Publication.PubNumber, dbo.WEEKENDING_TABLE.account_no, dbo.WEEKENDING_TABLE.S_Type_timestamp, dbo.WEEKENDING_TABLE.R_Type_timestamp, dbo.WEEKENDING_TABLE.edition_name > FROM dbo.WEEKENDING_TABLE INNER JOIN Publication ON dbo.WEEKENDING_TABLE.publication_name = Publication.Publication_Name > WHERE (dbo.WEEKENDING_TABLE.t_quantity - dbo.WEEKENDING_TABLE.t_allowance>0) > ORDER BY Publication.PubNumber > > SELECT REPORT_WKENDING.PubNumber, REPORT_WKENDING.publication_name, REPORT_WKENDING.calculated_price, SUM(REPORT_WKENDING.Copies) AS Copies, SUM(REPORT_WKENDING.Rev) AS Rev > FROM dbo.REPORT_WKENDING > Group by dbo.REPORT_WKENDING.PubNumber, dbo.REPORT_WKENDING.publication_name, dbo.REPORT_WKENDING.calculated_price > order by dbo.REPORT_WKENDING.PubNumber, dbo.REPORT_WKENDING.calculated_price > --order by dbo.REPORT_WKENDING.calculated_price > --compute sum (dbo.REPORT_WKENDING.Copies) by dbo.REPORT_WKENDING.calculated_price > --compute sum (dbo.REPORT_WKENDING.Rev) by dbo.REPORT_WKENDING.calculated_price > GO >
- Next message: Louis Davidson: "Re: update more than 1 col with SELECT"
- Previous message: Anith Sen: "Re: Using the "ConnectionID" Property"
- In reply to: Stephen Cairns: "Stored Procedures - Temporary Tables"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
