Dynamic Table creation
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
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
ASKER
how big is the performance hit ? if i have to do this every single time ?
>how big is the performance hit ? if i have to do this every single time ?
if to check if table is here - - just check sysobjects - no perf effect
there is sample of code:
declare @tabl varchar(100)
set @tabl = 'archive_' + convert(char(2),getdate(),
if object_id('+ @tabl+ ')is not null
Begin
-- here your you create table
end
Else
Begin
-- here your insert into
end
if to check if table is here - - just check sysobjects - no perf effect
there is sample of code:
declare @tabl varchar(100)
set @tabl = 'archive_' + convert(char(2),getdate(),
if object_id('+ @tabl+ ')is not null
Begin
-- here your you create table
end
Else
Begin
-- here your insert into
end
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BTW: if Sp used for creation of table
--use, for example
set @str= 'exec sp_createtable ' + @tabl
exec(@str)
--use, for example
set @str= 'exec sp_createtable ' + @tabl
exec(@str)
Hi,
Eugene describes how to to this pretty well, but I thought I would add a few snippets (from experience of the doing same thing)
Security can be an issue with creating tables, so I personally have a sql agent job that creates monthly tables in advance. of their requirement. The job is scheduled under a sysadmin account context.
If you want to be able to seemlessly query all the tables you may want to also dynamically create a partitioned vie across all the monthly tables. then you can use the view as a single entry point to query the tables.
More recently I have changed my strategy to have 1 database per month with a signe table in each called Archive.... Why have I done this? basically because very quickly with VLDBs you will hit the problem that your database becomes way to big to back up in a practical amount of time ( I deal with aprox 50million row per day). So splitting the table into seperate databases means that at the end of one month the database for that month becomes static, It can then be reindexed and shrunk and set readonly and then backed up once.
You will also find that using this technque, if and when disk space becomes a problem, you can detatch some of the monthly databases copy the db files to another drive (or even server) and reattach thus giving significantly greater flexibility for storage planning.
Again this is a stratagy I use. eg: my I have 3 months of data maintained on a SCSII Raid 10, after 3 months it is moved to SCSII Raid 5 San then finally after 9 months ends up on an IDE raid San. When our IDE Sam is full the the data will be likely never to be queried again (in our senario) and so it will then be streamed to ofline storage.
Eugene describes how to to this pretty well, but I thought I would add a few snippets (from experience of the doing same thing)
Security can be an issue with creating tables, so I personally have a sql agent job that creates monthly tables in advance. of their requirement. The job is scheduled under a sysadmin account context.
If you want to be able to seemlessly query all the tables you may want to also dynamically create a partitioned vie across all the monthly tables. then you can use the view as a single entry point to query the tables.
More recently I have changed my strategy to have 1 database per month with a signe table in each called Archive.... Why have I done this? basically because very quickly with VLDBs you will hit the problem that your database becomes way to big to back up in a practical amount of time ( I deal with aprox 50million row per day). So splitting the table into seperate databases means that at the end of one month the database for that month becomes static, It can then be reindexed and shrunk and set readonly and then backed up once.
You will also find that using this technque, if and when disk space becomes a problem, you can detatch some of the monthly databases copy the db files to another drive (or even server) and reattach thus giving significantly greater flexibility for storage planning.
Again this is a stratagy I use. eg: my I have 3 months of data maintained on a SCSII Raid 10, after 3 months it is moved to SCSII Raid 5 San then finally after 9 months ends up on an IDE raid San. When our IDE Sam is full the the data will be likely never to be queried again (in our senario) and so it will then be streamed to ofline storage.
http://www.sqlteam.com/item.asp?ItemID=4599
--to see if table not exists
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test222
begin
Create table test222...
end
--------------------or
if object_id('test223')is null
Begin
create table test223
End