First - I am NOT a SQL Server DBA. I've very limited experience with T-SQL - just queries and views. My SQL experience is from FoxPro/Access. I'm contracting to do primarily SSRS. But, a report I'm building is so complicated and takes so long to run, that it is impractical to run it from a view. There are 4 different datasets that I need - each one creates a table ~22MB of data space (100,000 rows). I would like to create tables using select statements, then delete them and re-create them overnight. I have several questions:
1. can I simply DROP TABLE?
2. can I do this in a stored procedure?
3. will I also have to re-create the indexes or is there a way to save them?
4. are there any storage issues I should be concerned about? (this process is on a "helper" server, not the server running the ERP - or the Report Server).
5. is there anything else I should be concerned about.
Thank you for good advice!