Stored Procedures - Temporary Tables

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_p rice) 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_p rice) 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.WEEKENDIN G_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

Who told you to use temp tables instead of stored procedures???
I have over 300 stored procedures just in one database, and they aren't taking up very much room at all.

SP’s are more promising the writing different- different insert queries.
So personally I think that use of SP help u to run code more speedy.

Note: but not write more then one insert query in one sp. Because it not support’s atomicity some time or in some cases. More no of SP dose not make a problem to u but more no of query can
dipit

My problem is more to do with inserting information into permanent tables. I seem to be transferring the data in large tables and replicating it in others. I need to be able to hold the information in temporary tables or delete the tables so as not much space is taken up on the server

Hi stevie,

Try This. The Code Given Below Will Create A Temperory Table Called #tmp_Sclassnoprice. The Scope Of The Table Is Only Within The Procedure. ie, U Won't Be Able To Access The Table Outside The Procedure. SQL Server Will Automatically Delete The Temperory Table #tmp_Sclassnoprice.
--------------------------------
CREATE PROCEDURE dbo.ByClassNoPrice @t_week_end_date smalldatetime
AS
Begin

SELECT dbo.cms_out.t_row_type, dbo.cms_out.t_account_ref_no, dbo.cms_out.account_no, dbo.cms_out.agent_name, dbo.cms_out.publication, dbo.cms_out.publication_name, dbo.cms_out.edition_name, dbo.cms_out.date, dbo.cms_out.t_quantity, dbo.cms_out.t_allowance, dbo.cms_out.calculated_price, dbo.cms_out.vat, dbo.cms_out.t_week_end_date, dbo.cms_out.day_of_week, dbo.cms_out.t_sort_col, dbo.cms_out.timestamp, dbo.dbo_Nagents_classification.Type Into #tmp_Sclassnoprice FROM dbo.dbo_Nagents_classification RIGHT JOIN dbo.cms_out ON dbo.dbo_Nagents_classification.account_no = dbo.cms_out.account_no
WHERE (dbo.cms_out.t_row_type = 'S')
AND (dbo.cms_out.t_week_end_date = @t_week_end_date)

End
----------------------------
Deepak

Keep the sproc, change all tables to table variables, and select from the week ending table variable to produce the report. if you need to keep the report data for history info, insert the data into a history table (perm) for safe keeping. The main thing to keep in mind here is to not thrash the server when running the sproc. Perm tables cause the dbto grow needlessly, thus causing expensive shrink operations to be ran. single pound temp tables live in tempdb, and can cause the server to bog down should a lot of data be moved into and out of them. Table variables live only in memory, and are extremely fast to move data into and out of.
good luck!

chenthorn is incorrect.

As can be seen in an excerpt from this Microsoft article:

Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?

A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).


See the rest of the article for more information on the subject.