Dynamic Table creation
Posted on 2005-04-22
I am wondering if there is a way to have a function or query in the stored procedure build in which
while check if a certain table already exists and if not create it by calling a stored procedure.
i have a job which runs every 1 hr to extract data from one table and moves it into an archive table.
The archive table grows by 10 million records per month which makes querys slower and i would like
to break the archive by month like this.
lets say a stored procedure is called to insert the row it should check based on a certain value called
inserttime if the tabel exists.
lets say the record has a inserttime of 03/31/2005 23:58:22 then it would check if table archive_032005 exists
if it does it will just insert the row. now the next record has a inserttime of 04/01/2005 01:02:32 now we check
if archive_042005 exists, since it does not we call sp_makearchivetable "042005" once it has been created we can
now insert data into the new table..
how can i code this so it does not take a huge hit, as well as how can i code the insert stored procedure in a way
that it can change the tablename based on the Value in the inserttime field