Dropping and Creating a Table Every Hour

[fpgiv]

Hi,
Is there a way to have SQL Server drop and Recreate a table based off a query every hour automatically?
Thanks

 

[donutman]

Scheduled job, stored proceedure. Truncate table not good enough?
-Karl

 

[SQLBill]

First, WHY?

Second, yes. You could create a job that runs once an hour. Have the job run a stored procedure that creates the table and include this prior to the CREATE TABLE:

IF EXISTS
 (SELECT NAME
  FROM dbname..SYSOBJECTS
  WHERE NAME = 'yourtablename')
DROP TABLE yourtablename
GO
CREATE TABLE yourtablename AS

-SQLBill

 

[fpgiv]

Thanks guys,
We have some data that occurs as the result of a huge query (the query takes 30+ seconds to run off the server), and we use it to generate reports. We figure it would be easier just to put the data in a table every hour and get it from there, as opposed to rerunning this huge query every time someone loads a web page.
That's why we wanted to do this every hour. If someone can suggest an alternate solution, I'd be very receptive.
Thanks!

 

[TJRTech]

In that case, DonutMans's suggestion of truncating the table rather than dropping and re-adding has merit as it is faster to execute.

TR

 

[fpgiv]

I don't know much about this...what does truncating the table do?

 

[TJRTech]

It removes records from the table at a lower level than a DELETE instruction, which places the deleted items in the transaction log. Truncate table avoids the transaction log which means you can't get the records back...but that is kinda the point in your case as you are about to recreate them.

TR

 

[SQLBill]

For more information on TRUNCATE TABLE command, check out the BOL. Use the Index tab, enter TRUNCATE TABLE and then pick the one that is for Transact SQL.

-SQLBill

BOL = Books OnLine = Microsoft SQL Server Help
Installed as part of the Client Tools.
Found at Start>Programs>Microsoft SQL Server>Books OnLine