Temporary Tables

Does anyone know how to re-write this query so I can create a temporary table to insert information as oppose to inserting info into the Sclassnoprice table and deleting its contents each time. It is taking up to much space on the server by doing this and i've been told to create temporary tables but not sure how to

CREATE PROCEDURE dbo.ByClassNoPrice
( @t_week_end_date smalldatetime,
)
AS
SET NOCOUNT ON
DELETE FROM dbo.Sclassnoprice

INSERT INTO Sclassnoprice(t_row_type, t_account_ref_no, account_no, agent_name, publication, publication_name, edition_name, date, t_quantity, t_allowance, calculated_price, vat, t_week_end_date, day_of_week, t_sort_col, timestamp, Type)
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
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);

GO

hello stevie,

there two case

first case creat tmp tabel + insert data + drop tmp table
sec case creat tmp tabel + insert data + delete data from tmp table

right click the table Sclassnoprice
(enterprice manager), select all task ,then generate SQL scritp... , click preview.
now change the lable/s Sclassnoprice with tmp_Sclassnoprice
paste in sp on 6 line (before DELETE FROM dbo.Sclassnoprice)

i am taking sec case, then your SP will be like this
**********************************************
CREATE PROCEDURE dbo.ByClassNoPrice
( @t_week_end_date smalldatetime,
)
AS
SET NOCOUNT ON

Paste here
DELETE FROM dbo.tmp_Sclassnoprice

INSERT INTO tmp_Sclassnoprice(t_row_type, t_account_ref_no, account_no, agent_name, publication, publication_name, edition_name, date, t_quantity, t_allowance, calculated_price, vat, t_week_end_date, day_of_week, t_sort_col, timestamp, Type)
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
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);

GO

**********************************************

if its done then plz let me know
dipit

Is something like this ok or am I on the wrong lines all together. Should I have the Drop table somewhere in here as well??


CREATE PROCEDURE dbo.ByClassNoPrice
( @t_week_end_date smalldatetime,
)
AS
SET NOCOUNT ON

CREATE TABLE [dbo].[tmp1_Sclassnoprice] (
[t_row_type] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[t_account_ref_no] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[account_no] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[agent_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[publication] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[publication_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[edition_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date] [smalldatetime] NULL ,
[t_quantity] [int] NULL ,
[t_allowance] [int] NULL ,
[calculated_price] [money] NULL ,
[vat] [decimal](18, 0) NULL ,
[t_week_end_date] [smalldatetime] NULL ,
[day_of_week] [int] NULL ,
[t_sort_col] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[timestamp] [datetime] NULL ,
[Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

DELETE FROM dbo.tmp1_Sclassnoprice

INSERT INTO tmp1_Sclassnoprice(t_row_type, t_account_ref_no, account_no, agent_name, publication, publication_name, edition_name, date, t_quantity, t_allowance, calculated_price, vat, t_week_end_date, day_of_week, t_sort_col, timestamp, Type)
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
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);

GO

see stevie,

i think that there is no need to drop the tabel.
bez if you create and drop a table every time then, you are giving a etc load on server, you can delete all the rows from table it is ok but dont drop it
dipit

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